t.dalpozzo@gmail.com | 25 Jun 18:19 2016
Picon

can't explain commit performance win7 vs linux : 8000/s vs 419/s

Hi,
I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64 
bit,  same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
I've in each partition a simple database with one table called data256 
with one column of 256 char.
I wrote a program using libpq which:
1 connects to 127.0.0.1 to the server
2 drops and recreates the table;
3 executes 2000 times the exec() function with the command  "INSERT INTO 
data256 VALUES ('AAAAAA...... 250 times')"
I want to commit after every insert of course.
The program is the same both in win and linux; in ansi c, so it's portable.

Performance:
Win7: 8000 write/sec
Linux: 419 write/sec

I don't figure out why such a difference. Also what should I expect? 
Which one is reasonable?

I compared the two postgresql.conf, they're identical (except obvious 
things), they're the default ones, I didn't touch them. I just tried to 
disable ssl in one because it was set but nothing changes.
I didn't go into deeper analysis because the source C file used for test 
is the same and the two postgresql.conf are identical.

Then, in order to test write / flush without postgres, I made another C 
program, to open a file in writing, and for 1000 times : write 256 bytes 
and flush them (using fsync in linux and FlushFileBuffers in win).
Win7: 200 write/sec
(Continue reading)

Craig James | 22 Jun 18:03 2016

Can't get two index scans

I'm working with a third-party plugin that does chemistry. It's very fast. However, I'm trying to do a sampling query, such as the first 1% of the database, and I just can't get the planner to create a good plan.  Here is the full query (the |>| operator does a subgraph match of a molecular substructure, in this case benzene, to find all molecules that have a benzene ring in the database):

explain analyze select * from version where smiles |>| 'c1ccccc1';
 ...
 Index Scan using i_version_smiles on version  (cost=3445.75..147094.03 rows=180283 width=36) (actual time=336.493..10015.753
 rows=180973 loops=1)
   Index Cond: (smiles |>| 'c1ccccc1'::molecule)
 Planning time: 1.228 ms
 Execution time: 10371.903 ms

Ten seconds over 263,000 molecules, which is actually good. Now let's limit it to the first 1% of the rows:

explain analyze select * from version where smiles |>| 'c1ccccc1' and version_id < 897630;
...
 Index Scan using pk_version on version  (cost=0.42..131940.05 rows=1643 width=36) (actual time=6.122..2816.298 rows=2039 loops=1)
   Index Cond: (version_id < 897630)
   Filter: (smiles |>| 'c1ccccc1'::molecule)
   Rows Removed by Filter: 590
 Planning time: 1.217 ms
 Execution time: 2822.117 ms

Notice that it doesn't use the i_version_smiles index at all, but instead applies the very expensive filter |>| to all 1% of the database. So instead of getting a 100x speedup, we only get a 3x speedup, about 30x worse that what is theoretically possible.

The production database is about 50x larger than this test database.

Maybe I misunderstand what's possible with indexes, but it seems to me that it could first do the pk_version index scan, and then use the results of that to do a limited index-scan search using the i_version_smiles index. Is that not possible? Is each index scan "self contained", that is, it doesn't take into account the results of another index scan?

Thanks,
Craig

jonescam | 20 Jun 16:00 2016
Gravatar

Looking for more Beta Users!

Hey guys!

We are looking for active beta participants to try out our new SaaS-BaseD
Monitoring Tool. Our tool will monitor your databases and their underlying
(virtual) infrastructure. If you would like to be a part of the beta, sign
up here: http://www.bluemedora.com/early-access/

We will initially be supporting MSSQL, Oracle, PostgreSQL, Mongo, DynamoDB
and MySQL (and MariaDB). And then we will add support to SQL Azure, DB2,
Aurora, RDS, etc. as the beta progresses.

If you have any questions, feel free to post and I will be happy to answer
them.

--
View this message in context: http://postgresql.nabble.com/Looking-for-more-Beta-Users-tp5908721.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

e.ratnakar.shetty | 17 Jun 17:19 2016
Picon

Savepoint and Releasepoint in Logs

Hi ,

 

I am connecting to PostgreSQL 9.4 via an ODBC driver on Windows machine from MS VBA application. I am facing huge performance issues while inserting data continuously. On analysing the logs , there were around 90000 statements related to Save Points and Release Points.

 

duration: 2.000 ms

2016-06-17 12:45:02 BST LOG:  statement: RELEASE _EXEC_SVP_1018CCF8

2016-06-17 12:45:02 BST LOG:  duration: 1.000 ms

2016-06-17 12:45:05 BST LOG:  statement: SAVEPOINT _EXEC_SVP_186EB5C8

2016-06-17 12:45:05 BST LOG:  duration: 0.000 ms

 

I am guessing these statements are causing an overhead while inserting records in to the table. Could you please let me know if I need to change any configuration settings to avoid creating any save points as the transaction is handled in the application.

 

Thanks

 

Regards,

Eisha Shetty

ACCENTURE | UK-NEWCASTLE

( +44 7741587433

* e.ratnakar.shetty <at> accenture.com

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

 

 

 

Adam Brusselback | 17 Jun 03:56 2016
Picon

9.6 query slower than 9.5.3

Hey all, testing out 9.6 beta 1 right now on Debian 8.5.

I have a query that is much slower on 9.6 than 9.5.3.

As a side note, when I explain analyze instead of just executing the query it takes more than 2x as long to run. I have tried looking for info on that online but have not found any.  Anyone know the reason for that?

The data is very close between the two servers, one is my production system so the only difference is slightly more added today since I set up the 9.6 server last night.

The query in question is here: 
SELECT cp.claim_id
, cp.claim_product_id
, cp.product_id
, cp.uom_type_id
, cp.rebate_requested_quantity
, cp.rebate_requested_rate
, cp.rebate_allowed_quantity
, cp.rebate_allowed_rate
, cp.distributor_company_id
, cp.resolve_date
FROM claim_product cp
INNER JOIN _claims_to_process x
ON cp.claim_id = x.claim_id
WHERE NOT EXISTS ( 
SELECT 1
FROM claim_product_reason_code r
WHERE r.claim_product_id = cp.claim_product_id
AND r.claim_reason_type = ANY (ARRAY['REJECT'::enum.claim_reason_type, 'OVERRIDE'::enum.claim_reason_type, 'RECALC'::enum.claim_reason_type])
AND upper_inf(r.active_range)
);

The query plan on 9.6 is here (disabled parallelism):
'Nested Loop  (cost=17574.63..30834.02 rows=1 width=106) (actual time=241.934..40332.190 rows=26994 loops=1)'
'  Join Filter: (cp.claim_id = x.claim_id)'
'  Rows Removed by Join Filter: 92335590'
'  ->  Hash Anti Join  (cost=17574.63..30808.68 rows=1 width=106) (actual time=173.742..586.805 rows=102171 loops=1)'
'        Hash Cond: (cp.claim_product_id = r.claim_product_id)'
'        ->  Seq Scan on claim_product cp  (cost=0.00..6714.76 rows=202076 width=106) (actual time=0.028..183.376 rows=202076 loops=1)'
'        ->  Hash  (cost=16972.49..16972.49 rows=48171 width=16) (actual time=173.436..173.436 rows=99905 loops=1)'
'              Buckets: 131072 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 5708kB'
'              ->  Bitmap Heap Scan on claim_product_reason_code r  (cost=4398.71..16972.49 rows=48171 width=16) (actual time=25.278..127.540 rows=99905 loops=1)'
'                    Recheck Cond: ((claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[])) AND upper_inf(active_range))'
'                    Heap Blocks: exact=10067'
'                    ->  Bitmap Index Scan on claim_product_reason_code_active_range_idx  (cost=0.00..4386.67 rows=48171 width=0) (actual time=23.174..23.174 rows=99905 loops=1)'
'                          Index Cond: (claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[]))'
'  ->  Seq Scan on _claims_to_process x  (cost=0.00..14.04 rows=904 width=16) (actual time=0.005..0.182 rows=904 loops=102171)'
'Planning time: 1.934 ms'
'Execution time: 40337.858 ms'

The 9.5.3 plan is here:
'Hash Anti Join  (cost=19884.53..39281.57 rows=30681 width=106) (actual time=848.791..978.036 rows=27354 loops=1)'
'  Hash Cond: (cp.claim_product_id = r.claim_product_id)'
'  ->  Nested Loop  (cost=0.42..17990.36 rows=41140 width=106) (actual time=0.132..106.333 rows=28775 loops=1)'
'        ->  Seq Scan on _claims_to_process x  (cost=0.00..27.00 rows=1700 width=16) (actual time=0.037..0.465 rows=923 loops=1)'
'        ->  Index Scan using idx_claim_product_claim_id on claim_product cp  (cost=0.42..10.33 rows=24 width=106) (actual time=0.015..0.093 rows=31 loops=923)'
'              Index Cond: (claim_id = x.claim_id)'
'  ->  Hash  (cost=19239.13..19239.13 rows=51599 width=16) (actual time=848.263..848.263 rows=100024 loops=1)'
'        Buckets: 131072 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 5713kB'
'        ->  Bitmap Heap Scan on claim_product_reason_code r  (cost=6240.64..19239.13 rows=51599 width=16) (actual time=31.505..782.799 rows=100024 loops=1)'
'              Recheck Cond: ((claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[])) AND upper_inf(active_range))'
'              Heap Blocks: exact=6261'
'              ->  Bitmap Index Scan on claim_product_reason_code_active_range_idx  (cost=0.00..6227.74 rows=51599 width=0) (actual time=30.231..30.231 rows=100051 loops=1)'
'                    Index Cond: (claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[]))'
'Planning time: 1.691 ms'
'Execution time: 982.667 ms'


Just for fun I set enable_nestloop=false on 9.6 and this is the plan I get:
'Hash Join  (cost=17599.97..30834.04 rows=1 width=106) (actual time=108.892..349.885 rows=26994 loops=1)'
'  Hash Cond: (cp.claim_id = x.claim_id)'
'  ->  Hash Anti Join  (cost=17574.63..30808.68 rows=1 width=106) (actual time=107.464..316.527 rows=102171 loops=1)'
'        Hash Cond: (cp.claim_product_id = r.claim_product_id)'
'        ->  Seq Scan on claim_product cp  (cost=0.00..6714.76 rows=202076 width=106) (actual time=0.011..61.230 rows=202076 loops=1)'
'        ->  Hash  (cost=16972.49..16972.49 rows=48171 width=16) (actual time=107.315..107.315 rows=99905 loops=1)'
'              Buckets: 131072 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 5708kB'
'              ->  Bitmap Heap Scan on claim_product_reason_code r  (cost=4398.71..16972.49 rows=48171 width=16) (actual time=23.478..68.644 rows=99905 loops=1)'
'                    Recheck Cond: ((claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[])) AND upper_inf(active_range))'
'                    Heap Blocks: exact=10067'
'                    ->  Bitmap Index Scan on claim_product_reason_code_active_range_idx  (cost=0.00..4386.67 rows=48171 width=0) (actual time=21.475..21.475 rows=99905 loops=1)'
'                          Index Cond: (claim_reason_type = ANY ('{REJECT,OVERRIDE,RECALC}'::enum.claim_reason_type[]))'
'  ->  Hash  (cost=14.04..14.04 rows=904 width=16) (actual time=0.937..0.937 rows=904 loops=1)'
'        Buckets: 1024  Batches: 1  Memory Usage: 51kB'
'        ->  Seq Scan on _claims_to_process x  (cost=0.00..14.04 rows=904 width=16) (actual time=0.022..0.442 rows=904 loops=1)'
'Planning time: 1.475 ms'
'Execution time: 353.958 ms'


meike.talbach | 16 Jun 09:58 2016

Index not used

Hello,
 
I've a basic table with about 100K rows:
 

CREATE TABLE "public"."push_topic" (
 "id" Serial PRIMARY KEY,
 "guid" public.push_guid NOT NULL,
 "authenticatorsending" Varchar(32) NOT NULL,
 "authenticatorsubscription" Varchar(32) NOT NULL,
 "countpushed" Integer NOT NULL,
 "datecreated" timestamp NOT NULL,
 "datelastpush" timestamp
)
CREATE UNIQUE INDEX push_topic_idx_topicguid ON push_topic
  USING btree (guid)

 
When I query this through pgsql, the queries are fast as expected.

This is the query:

select * from push_topic where guid = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'

And the plan:

Index Scan using push_topic_idx_topicguid on push_topic  (cost=0.42..8.44 rows=1 width=103) (actual
time=0.117..0.121 rows=1 loops=1)
  Index Cond: ((guid)::bpchar = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar)
  Buffers: shared hit=3 read=1
Total runtime: 0.191 ms

However when I run the exact query through a different application (CodeSynthesis ORM) the query is very
slow (~ 115ms logged)
I noted this is due to a sequential scan happening on the table instead of an index scan.

This is query plan in the log file:

LOG:  plan:
DETAIL:     {PLANNEDSTMT 
	   :commandType 1 
	   :queryId 0 
	   :hasReturning false 
	   :hasModifyingCTE false 
	   :canSetTag true 
	   :transientPlan false 
	   :planTree 
	      {SEQSCAN 
	      :startup_cost 0.00 
	      :total_cost 2877.58 
	      :plan_rows 429 
	      :plan_width 103 
	      :targetlist (
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 23 
	            :vartypmod -1 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 7
	            }
	         :resno 1 
	         :resname id 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 1 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 2 
	            :vartype 16385 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 2 
	            :location 26
	            }
	         :resno 2 
	         :resname guid 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 2 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 3 
	            :vartype 1043 
	            :vartypmod 36 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 3 
	            :location 47
	            }
	         :resno 3 
	         :resname authenticatorsending 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 3 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 4 
	            :vartype 1043 
	            :vartypmod 36 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 4 
	            :location 84
	            }
	         :resno 4 
	         :resname authenticatorsubscription 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 4 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 5 
	            :vartype 23 
	            :vartypmod -1 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 5 
	            :location 126
	            }
	         :resno 5 
	         :resname countpushed 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 5 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 6 
	            :vartype 1114 
	            :vartypmod -1 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 6 
	            :location 154
	            }
	         :resno 6 
	         :resname datecreated 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 6 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 7 
	            :vartype 1114 
	            :vartypmod -1 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 7 
	            :location 182
	            }
	         :resno 7 
	         :resname datelastpush 
	         :ressortgroupref 0 
	         :resorigtbl 16393 
	         :resorigcol 7 
	         :resjunk false
	         }
	      )
	      :qual (
	         {OPEXPR 
	         :opno 98 
	         :opfuncid 67 
	         :opresulttype 16 
	         :opretset false 
	         :opcollid 0 
	         :inputcollid 100 
	         :args (
	            {FUNCEXPR 
	            :funcid 401 
	            :funcresulttype 25 
	            :funcretset false 
	            :funcvariadic false 
	            :funcformat 2 
	            :funccollid 100 
	            :inputcollid 100 
	            :args (
	               {VAR 
	               :varno 1 
	               :varattno 2 
	               :vartype 16385 
	               :vartypmod -1 
	               :varcollid 100 
	               :varlevelsup 0 
	               :varnoold 1 
	               :varoattno 2 
	               :location 234
	               }
	            )
	            :location -1
	            }
	            {CONST 
	            :consttype 25 
	            :consttypmod -1 
	            :constcollid 100 
	            :constlen -1 
	            :constbyval false 
	            :constisnull false 
	            :location -1 
	            :constvalue 40 [ -96 0 0 0 48 48 53 51 54 49 69 56 45 51 51 69 65 
	            45 49 70 48 69 45 66 50 49 55 45 67 57 49 66 52 65 67 55 66 67 69 
	            54 ]
	            }
	         )
	         :location 254
	         }
	      )
	      :lefttree <> 
	      :righttree <> 
	      :initPlan <> 
	      :extParam (b)
	      :allParam (b)
	      :scanrelid 1
	      }
	   :rtable (
	      {RTE 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname push_topic 
	         :colnames ("id" "guid" "authenticatorsending" "authenticatorsubscript
	         ion" "countpushed" "datecreated" "datelastpush")
	         }
	      :rtekind 0 
	      :relid 16393 
	      :relkind r 
	      :lateral false 
	      :inh false 
	      :inFromCl true 
	      :requiredPerms 2 
	      :checkAsUser 0 
	      :selectedCols (b 9 10 11 12 13 14 15)
	      :modifiedCols (b)
	      }
	   )
	   :resultRelations <> 
	   :utilityStmt <> 
	   :subplans <> 
	   :rewindPlanIDs (b)
	   :rowMarks <> 
	   :relationOids (o 16393)
	   :invalItems <> 
	   :nParamExec 0
	   }
	
STATEMENT:  SELECT "push_topic"."id", "push_topic"."guid", "push_topic"."authenticatorsending",
"push_topic"."authenticatorsubscription", "push_topic"."countpushed",
"push_topic"."datecreated", "push_topic"."datelastpush" FROM "push_topic" WHERE
"push_topic"."guid" = $1
LOG:  duration: 115.498 ms  execute query_mc_push_database_Topic: SELECT "push_topic"."id",
"push_topic"."guid", "push_topic"."authenticatorsending",
"push_topic"."authenticatorsubscription", "push_topic"."countpushed",
"push_topic"."datecreated", "push_topic"."datelastpush" FROM "push_topic" WHERE
"push_topic"."guid" = $1

Any idea how to solve this ?

Thank you

Meike

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Adrian Myers | 16 Jun 00:00 2016
Picon

pg_restore seems very slow

This is my first post to the mailing list, so I apologize for any etiquette issues.

I have a few databases that I am trying to move from one system to another.  Both systems are running Windows 7 and Postgres 8.4, and they are pretty powerful machines (40-core Xeon workstations with decent hardware across the board). While the DBs vary in size, I'm working right now with one that is roughly 50 tables and probably 75M rows, and is about 300MB on disk when exported via pg_dump. 

I am exporting and restoring using these commands (on separate sytems):
pg_dump -F c mydb > mydb.dump
pg_restore -C -j 10 mydb.dump

The dump process runs in about a minute and seems fine. The restore process has already been running for around 7 hours.

Yesterday, I tried restoring a larger DB that is roughly triple the dimensions listed above, and it ran for over 16 hours without completing.

I followed the advice given at http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html and set the conf settings as directed and restarted the server.

You can see in the command line that I am trying to use the -j parameter for parallelism, but I don't see much evidence of that in Task Manager. CPU load is consistently 1 or 2% and only a couple cores seem to be doing anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to look for pg_restore's disk I/O, but there is an entry for pg_restore in Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write Bytes. Since that's just the parent process that might make sense but I don't see much activity elsewhere either.

Is there something simple that I am missing here? Does the -j flag not work in 8.4 and I should use --jobs? It just seems like none of the CPU or RAM usage I'd expect from this process are evident, it's taking many times longer than I would expect, and I don't know how to verify if the things I'm trying are working or not.

Any insight would be appreciated!

Thanks,
Adrian
Ivan Voras | 15 Jun 11:34 2016
Picon
Gravatar

Indexes for hashes

Hi,

I have an application which stores a large amounts of hex-encoded hash strings (nearly 100 GB of them), which means:
  • The number of distinct characters (alphabet) is limited to 16
  • Each string is of the same length, 64 characters
  • The strings are essentially random
Creating a B-Tree index on this results in the index size being larger than the table itself, and there are disk space constraints.

I've found the SP-GIST radix tree index, and thought it could be a good match for the data because of the above constraints. An attempt to create it (as in CREATE INDEX ON t USING spgist(field_name)) apparently takes more than 12 hours (while a similar B-tree index takes a few hours at most), so I've interrupted it because "it probably is not going to finish in a reasonable time". Some slides I found on the spgist index allude that both build time and size are not really suitable for this purpose.

My question is: what would be the most size-efficient index for this situation?
Rowan Seymour | 10 Jun 15:04 2016
Picon
Gravatar

Many-to-many performance problem

In our Django app we have messages (currently about 7 million in table msgs_message) and labels (about 300), and a join table to associate messages with labels (about 500,000 in msgs_message_labels). Not sure you'll need them, but here are the relevant table schemas:

CREATE TABLE msgs_message
(
    id INTEGER PRIMARY KEY NOT NULL,
    type VARCHAR NOT NULL,
    text TEXT NOT NULL,
    is_archived BOOLEAN NOT NULL,
    created_on TIMESTAMP WITH TIME ZONE NOT NULL,
    contact_id INTEGER NOT NULL,
    org_id INTEGER NOT NULL,
    case_id INTEGER,
    backend_id INTEGER NOT NULL,
    is_handled BOOLEAN NOT NULL,
    is_flagged BOOLEAN NOT NULL,
    is_active BOOLEAN NOT NULL,
    has_labels BOOLEAN NOT NULL,
    CONSTRAINT msgs_message_contact_id_5c8e3f216c115643_fk_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES contacts_contact (id),
    CONSTRAINT msgs_message_org_id_81a0adfcc99151d_fk_orgs_org_id FOREIGN KEY (org_id) REFERENCES orgs_org (id),
    CONSTRAINT msgs_message_case_id_51998150f9629c_fk_cases_case_id FOREIGN KEY (case_id) REFERENCES cases_case (id)
);
CREATE UNIQUE INDEX msgs_message_backend_id_key ON msgs_message (backend_id);
CREATE INDEX msgs_message_6d82f13d ON msgs_message (contact_id);
CREATE INDEX msgs_message_9cf869aa ON msgs_message (org_id);
CREATE INDEX msgs_message_7f12ca67 ON msgs_message (case_id);

CREATE TABLE msgs_message_labels
(
    id INTEGER PRIMARY KEY NOT NULL,
    message_id INTEGER NOT NULL,
    label_id INTEGER NOT NULL,
    CONSTRAINT msgs_message_lab_message_id_1dfa44628fe448dd_fk_msgs_message_id FOREIGN KEY (message_id) REFERENCES msgs_message (id),
    CONSTRAINT msgs_message_labels_label_id_77cbdebd8d255b7a_fk_msgs_label_id FOREIGN KEY (label_id) REFERENCES msgs_label (id)
);
CREATE UNIQUE INDEX msgs_message_labels_message_id_label_id_key ON msgs_message_labels (message_id, label_id);
CREATE INDEX msgs_message_labels_4ccaa172 ON msgs_message_labels (message_id);
CREATE INDEX msgs_message_labels_abec2aca ON msgs_message_labels (label_id);

Users can search for messages, and they are returned page by page in reverse chronological order. There are several partial multi-column indexes on the message table, but the one used for the example queries below is

CREATE INDEX msgs_inbox ON msgs_message(org_id, created_on DESC)
WHERE is_active = TRUE AND is_handled = TRUE AND is_archived = FALSE AND has_labels = TRUE;

So a typical query for the latest page of messages looks like (https://explain.depesz.com/s/G9ew):

SELECT "msgs_message".* 
FROM "msgs_message" 
WHERE ("msgs_message"."org_id" = 7 
    AND "msgs_message"."is_active" = true 
    AND "msgs_message"."is_handled" = true 
    AND "msgs_message"."has_labels" = true 
    AND "msgs_message"."is_archived" = false 
    AND "msgs_message"."created_on" < '2016-06-10T07:11:06.381000+00:00'::timestamptz
) ORDER BY "msgs_message"."created_on" DESC LIMIT 50

But users can also search for messages that have one or more labels, leading to queries that look like:

SELECT DISTINCT "msgs_message".* 
FROM "msgs_message" 
INNER JOIN "msgs_message_labels" ON ( "msgs_message"."id" = "msgs_message_labels"."message_id" ) 
WHERE ("msgs_message"."org_id" = 7 
    AND "msgs_message"."is_active" = true 
    AND "msgs_message"."is_handled" = true 
    AND "msgs_message_labels"."label_id" IN (127, 128, 135, 136, 137, 138, 140, 141, 143, 144) 
    AND "msgs_message"."has_labels" = true 
    AND "msgs_message"."is_archived" = false 
    AND "msgs_message"."created_on" < '2016-06-10T07:11:06.381000+00:00'::timestamptz
) ORDER BY "msgs_message"."created_on" DESC LIMIT 50

Most of time, this query performs like https://explain.depesz.com/s/ksOC (~15ms). It's no longer using the using the msgs_inbox index, but it's plenty fast. However, sometimes it performs like https://explain.depesz.com/s/81c (67000ms)

And if you run it again, it'll be fast again. Am I correct in interpreting that second explain as being slow because msgs_message_pkey isn't cached? It looks like it read from that index 3556 times, and each time took 18.559 (?) ms, and that adds up to 65,996ms. The database server says it has lots of free memory so is there something I should be doing to keep that index in memory?

Generally speaking, is there a good strategy for optimising queries like these which involve two tables?
  • I tried moving the label references into an int array on msgs_message, and then using btree_gin to create a multi-column index involving the array column, but that doesn't appear to be very useful for these ordered queries because it's not an ordered index.
  • I tried adding created_on to msgs_message_labels table but I couldn't find a way of avoiding the in-memory sort.
  • Have thought about dynamically creating partial indexes for each label using an array column on msgs_message to hold label ids, and index condition like WHERE label_ids && ARRAY[123] but not sure what other problems I'll run into with hundreds of indexes on the same table.
Server is an Amazon RDS instance with default settings and Postgres 9.3.10, with one other database in the instance.

All advice very much appreciated, thanks

--
Rowan Seymour | +260 964153686
Ed Felstein | 8 Jun 06:57 2016
Picon

Performance of LIKE/NOT LIKE when used in single query

Hello,
First time poster here.  Bear with me.
Using PostgreSQL 9.5
I have a situation where I have a LIKE and a NOT LIKE in the same query to identify strings in a varchar field.  Since I am using wildcards, I have created a GIN index on the field in question, which makes LIKE '%xxxx%' searches run very fast.  The problem is the NOT LIKE phrases, which (as would be expected) force a sequential scan.  Being that we're talking about millions of records, this is not desirable.
Here's the question...
Is there a way, using a single query, to emulate the process of running the LIKE part first, then running the NOT LIKE just on those results?  I can accomplish this in a multi-step process by separating the single query into two queries, populating a temporary table with the results of the LIKEs, then running the NOT LIKEs on the temporary table.  For various reasons, this is not the ideal solution for me.
Or is there another approach that would accomplish the same thing with the same level of performance?

Rafał Gutkowski | 7 Jun 15:39 2016
Picon
Gravatar

Combination of partial and full indexes

Hi.

I had a fight with a query planner because it doesn’t listen.

There are two indexes:

 - with expression in descending order:
    "offers_offer_next_update_idx" btree (offer_next_update(update_ts, update_freq) DESC) WHERE o_archived = false
 - unique with two columns:
    "offers_source_id_o_key_idx" UNIQUE, btree (source_id, o_key)

Here's the query with filter for offers.source_id columns which
is pretty slow because "offers_source_id_o_key_idx" is not used:

    EXPLAIN ANALYZE
    SELECT offers.o_url AS offers_o_url
    FROM offers
    WHERE offers.source_id = 1 AND offers.o_archived = false AND now() > offer_next_update(offers.update_ts, offers.update_freq)
    ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
    LIMIT 1000;

    Limit  (cost=0.68..23403.77 rows=1000 width=116) (actual time=143.544..147.870 rows=1000 loops=1)
      ->  Index Scan using offers_offer_next_update_idx on offers  (cost=0.68..1017824.69 rows=43491 width=116) (actual time=143.542..147.615 rows=1000 loops=1)
            Index Cond: (now() > offer_next_update(update_ts, update_freq))
            Filter: (source_id = 1)
            Rows Removed by Filter: 121376
    Total runtime: 148.023 ms


When I remove filter on offers.source_id, query plan looks like this:

    EXPLAIN ANALYZE
    SELECT offers.o_url AS offers_o_url
    FROM offers
    WHERE offers.o_archived = false AND now() > offer_next_update(offers.update_ts, offers.update_freq)
    ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
    LIMIT 1000;

    Limit  (cost=0.68..4238.27 rows=1000 width=116) (actual time=0.060..3.877 rows=1000 loops=1)
      ->  Index Scan using offers_offer_next_update_idx on offers  (cost=0.68..1069411.78 rows=252363 width=116) (actual time=0.058..3.577 rows=1000 loops=1)
            Index Cond: (now() > offer_next_update(update_ts, update_freq))
    Total runtime: 4.031 ms


I even tried to change orders of conditions in second query but it doesn't seem
to make a difference for a planner.

Shouldn't query planner use offers_source_id_o_key_idx to speed up query above?


PostgreSQL version: PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit

Configuration:
             name             |            current_setting             |        source
------------------------------+----------------------------------------+----------------------
 application_name             | psql                                   | client
 checkpoint_completion_target | 0.9                                    | configuration file
 checkpoint_segments          | 3                                      | configuration file
 client_encoding              | UTF8                                   | client
 DateStyle                    | ISO, MDY                               | configuration file
 default_text_search_config   | pg_catalog.english                     | configuration file
 effective_cache_size         | 128MB                                  | configuration file
 external_pid_file            | /var/run/postgresql/9.3-main.pid       | configuration file
 lc_messages                  | en_US.UTF-8                            | configuration file
 lc_monetary                  | en_US.UTF-8                            | configuration file
 lc_numeric                   | en_US.UTF-8                            | configuration file
 lc_time                      | en_US.UTF-8                            | configuration file
 max_connections              | 100                                    | configuration file
 max_locks_per_transaction    | 168                                    | configuration file
 max_stack_depth              | 2MB                                    | environment variable
 port                         | 5432                                   | configuration file
 shared_buffers               | 4GB                                    | configuration file
 temp_buffers                 | 12MB                                   | configuration file
 unix_socket_directories      | /var/run/postgresql                    | configuration file
 work_mem                     | 16MB                                   | configuration file


Definitions:

CREATE OR REPLACE FUNCTION public.offer_next_update(last timestamp without time zone, minutes smallint)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
BEGIN
        RETURN last + (minutes || ' min')::interval;
END
$function$



Gmane