From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Two fast searches turn slow when used with OR clause
Date: Thu, 05 Aug 2010 11:34:45 -0700
Message-ID: <4C5B0445.2000405@emolecules.com> (raw)
In-Reply-To: <AANLkTinansOsoUOtYVAOORoDwhKJPVXFo9S3ZfgSUMnz@mail.gmail.com>
References: <k2pda0294261004061437p9cd3810dqe9ce528b6ddb0873@mail.gmail.com>
<m2m603c8f071004071413w1f2d2497hbbe07d9c6491848f@mail.gmail.com>
<4BBCFB12.1010306@emolecules.com>
<y2r72dbd3151004071556j22244233l8fa1db295b7f9cd3@mail.gmail.com>
<r2q603c8f071004071747n3e56d12bo11c49885b8c2eb2e@mail.gmail.com>
<4BBD3A17.90209@emolecules.com>
<y2l72dbd3151004071950kfe09a98fqf0c9a136375260e2@mail.gmail.com>
<v2u603c8f071004072007t323fe469lfdd8fa5f2a283890@mail.gmail.com>
<4C23FD52.40508@emolecules.com>
<23596.1277438692@sss.pgh.pa.us>
<4C24B3BB.3070901@emolecules.com>
<2286.1277477230@sss.pgh.pa.us>
<4C24D679.7030701@emolecules.com>
<6203.1277485291@sss.pgh.pa.us>
<4C24FB0D.4020701@emolecules.com>
<4C24FD82.4070100@2ndquadrant.com>
<4C28D809.7070707@emolecules.com>
<AANLkTinansOsoUOtYVAOORoDwhKJPVXFo9S3ZfgSUMnz@mail.gmail.com>
I can query either my PARENT table joined to PRICES, or my VERSION table joined to PRICES, and get an answer in 30-40 msec. But put the two together, it jumps to 4 seconds. What am I missing here? I figured this query would be nearly instantaneous. The VERSION.ISOSMILES and PARENT.ISOSMILES columns both have unique indexes. Instead of using these indexes, it's doing a full-table scan of both tables, even though there can't possibly be more than one match in each table.
I guess I could rewrite this as a UNION of the two subqueries, but that seems contrived.
This is PG 8.3.10 on Linux.
Thanks,
Craig
=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, vn.version_id
-> from plus p join sample s
-> on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
-> join version vn on (s.version_id = vn.version_id) join parent pn
-> on (s.parent_id = pn.parent_id)
-> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
-> or pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
-> order by price;
Sort (cost=71922.00..71922.00 rows=1 width=19) (actual time=4337.114..4337.122 rows=10 loops=1)
Sort Key: p.price Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=18407.53..71921.99 rows=1 width=19) (actual time=1122.685..4337.028 rows=10 loops=1)
-> Hash Join (cost=18407.53..71903.71 rows=4 width=20) (actual time=1122.624..4336.682 rows=7 loops=1)
Hash Cond: (s.version_id = vn.version_id)
Join Filter: ((vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text) OR (pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text))
-> Hash Join (cost=8807.15..44470.73 rows=620264 width=54) (actual time=431.501..2541.329 rows=620264 loops=1)
Hash Cond: (s.parent_id = pn.parent_id)
-> Seq Scan on sample s (cost=0.00..21707.64 rows=620264 width=24) (actual time=0.008..471.340 rows=620264 loops=1)
-> Hash (cost=5335.40..5335.40 rows=277740 width=38) (actual time=431.166..431.166 rows=277740 loops=1)
-> Seq Scan on parent pn (cost=0.00..5335.40 rows=277740 width=38) (actual time=0.012..195.822 rows=277740 loops=1)
-> Hash (cost=5884.06..5884.06 rows=297306 width=38) (actual time=467.267..467.267 rows=297306 loops=1)
-> Seq Scan on version vn (cost=0.00..5884.06 rows=297306 width=38) (actual time=0.017..215.285 rows=297306 loops=1)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.039..0.041 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 4344.222 ms
(17 rows)
If I only query the VERSION table, it's very fast:
x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, vn.version_id
-> from plus p
-> join sample s on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
-> join version vn on (s.version_id = vn.version_id)
-> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;
Sort (cost=45.73..45.74 rows=1 width=19) (actual time=32.438..32.448 rows=10 loops=1)
Sort Key: p.price
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..45.72 rows=1 width=19) (actual time=32.309..32.411 rows=10 loops=1)
-> Nested Loop (cost=0.00..36.58 rows=2 width=20) (actual time=32.295..32.319 rows=7 loops=1)
-> Index Scan using i_version_isosmiles on version vn (cost=0.00..8.39 rows=1 width=4) (actual time=32.280..32.281 rows=1 loops=1)
Index Cond: (isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text)
-> Index Scan using i_sample_version_id on sample s (cost=0.00..28.12 rows=6 width=20) (actual time=0.011..0.024 rows=7 loops=1)
Index Cond: (s.version_id = vn.version_id)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.010..0.011 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 32.528 ms
(12 rows)
Same good performance if I only query the PARENT table:
x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, pn.parent_id from plus p join sample s on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id) join parent pn on (s.parent_id = pn.parent_id) where pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=57.73..57.74 rows=1 width=19) (actual time=43.564..43.564 rows=10 loops=1)
Sort Key: p.price
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..57.72 rows=1 width=19) (actual time=43.429..43.537 rows=10 loops=1)
-> Nested Loop (cost=0.00..48.58 rows=2 width=20) (actual time=43.407..43.430 rows=7 loops=1)
-> Index Scan using i_parent_isosmiles on parent pn (cost=0.00..8.38 rows=1 width=4) (actual time=27.342..27.343 rows=1 loops=1)
Index Cond: (isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text)
-> Index Scan using i_sample_parent_id on sample s (cost=0.00..40.09 rows=9 width=20) (actual time=16.057..16.070 rows=7 loops=1)
Index Cond: (s.parent_id = pn.parent_id)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.010..0.011 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 43.628 ms
x=> \d version
Table "x.version"
Column | Type | Modifiers
------------+---------+-----------
version_id | integer | not null
parent_id | integer | not null
isosmiles | text | not null
coord_2d | text |
Indexes:
"version_pkey" PRIMARY KEY, btree (version_id)
"i_version_isosmiles" UNIQUE, btree (isosmiles)
"i_version_parent_id" btree (parent_id)
Foreign-key constraints:
"fk_parent" FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ON DELETE CASCADE
x=> \d parent
Table "x.parent"
Column | Type | Modifiers
-----------+---------+-----------
parent_id | integer | not null
isosmiles | text | not null
coord_2d | text |
Indexes:
"parent_pkey" PRIMARY KEY, btree (parent_id)
"i_parent_isosmiles" UNIQUE, btree (isosmiles)
=> \d sample
Table "reaxys.sample"
Column | Type | Modifiers
--------------------+---------+-----------------------------------------------------
sample_id | integer | not null default nextval('sample_id_seq'::regclass)
sample_id_src | integer |
parent_id | integer | not null
version_id | integer | not null
supplier_id | integer | not null
catalogue_id | integer | not null
catalogue_issue_id | integer | not null
load_id | integer | not null
load_file_id | integer |
compound_id | text | not null
cas_number | text |
purity | text |
chemical_name | text |
url | text |
price_code | text |
comment | text |
salt_comment | text |
Indexes:
"sample_pkey" PRIMARY KEY, btree (sample_id)
"i_sample_casno" btree (cas_number)
"i_sample_catalogue_id" btree (catalogue_id)
"i_sample_catalogue_issue_id" btree (catalogue_issue_id)
"i_sample_chem_name" btree (chemical_name)
"i_sample_compound_id" btree (compound_id)
"i_sample_load_id" btree (load_id)
"i_sample_parent_id" btree (parent_id)
"i_sample_sample_id_src" btree (sample_id_src)
"i_sample_supplier_id" btree (supplier_id)
"i_sample_version_id" btree (version_id)
Foreign-key constraints:
"fk_item" FOREIGN KEY (version_id) REFERENCES version(version_id) ON DELETE CASCADE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Two fast searches turn slow when used with OR clause
In-Reply-To: <4C5B0445.2000405@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox