pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin vs. subquery
4+ messages / 4 participants
[nested] [flat]
* join vs. subquery
@ 2005-02-22 06:47 David Haas <dave@modelpredictivesystems.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Haas @ 2005-02-22 06:47 UTC (permalink / raw)
To: pgsql-performance
Hi -
This is based on a discussion I was having with neilc on IRC. He
suggested I post it here. Sorry for the length - I'm including
everything he requested.
I'm comparing the speeds of the following two queries. I was curious
why query 1 was faster than query 2:
query 1:
Select layer_number
FROM batch_report_index
WHERE device_id = (SELECT device_id FROM device_index WHERE device_name
='CP8M')
AND technology_id = (SELECT technology_id FROM technology_index WHERE
technology_name = 'G12');
query 2:
Select b.layer_number
FROM batch_report_index b, device_index d, technology_index t
WHERE b.device_id = d.device_id
AND b.technology_id = t.technology_id
AND d.device_name = 'CP8M'
AND t.technology_name = 'G12';
Here were my first runs:
(query 1 explain analyze)
Seq Scan on batch_report_index (cost=6.05..12370.66 rows=83 width=4)
(actual time=19.274..1903.110 rows=61416 loops=1)
Filter: ((device_id = $0) AND (technology_id = $1))
InitPlan
-> Index Scan using device_index_device_name_key on device_index
(cost=0.00..4.88 rows=1 width=4) (actual time=0.310..0.320 rows=1
loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Seq Scan on technology_index (cost=0.00..1.18 rows=1 width=4)
(actual time=0.117..0.149 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 1947.896 ms
(8 rows)
(query 2 explain analyze)
Hash Join (cost=6.06..12380.70 rows=46 width=4) (actual
time=35.509..2831.685 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Hash Join (cost=4.88..12375.87 rows=638 width=8) (actual
time=34.584..2448.862 rows=61416 loops=1)
Hash Cond: ("outer".device_id = "inner".device_id)
-> Seq Scan on batch_report_index b (cost=0.00..10182.74
rows=436374 width=12) (actual time=0.100..1373.085 rows=436374 loops=1)
-> Hash (cost=4.88..4.88 rows=1 width=4) (actual
time=0.635..0.635 rows=0 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.505..0.520 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.348..0.348
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.198..0.239 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 2872.252 ms
(12 rows)
On neilc's suggestion, I did a vacuum analyze, then turned off hash
joins. Here's query 2, no hash joins:
(query 2 explain analyze)
Nested Loop (cost=0.00..15651.44 rows=46 width=4) (actual
time=22.079..2741.103 rows=61416 loops=1)
Join Filter: ("inner".technology_id = "outer".technology_id)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1 width=4)
(actual time=0.178..0.218 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
-> Nested Loop (cost=0.00..15642.29 rows=638 width=8) (actual
time=21.792..2530.470 rows=61416 loops=1)
Join Filter: ("inner".device_id = "outer".device_id)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.331..0.346 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Seq Scan on batch_report_index b (cost=0.00..10182.74
rows=436374 width=12) (actual time=0.070..1437.938 rows=436374 loops=1)
Total runtime: 2782.628 ms
(10 rows)
He then suggested I turn hash_joins back on and put an index on the
batch_report_table's device_id. Here's query 2 again:
(query 2 explain analyze)
Hash Join (cost=1.18..2389.06 rows=46 width=4) (actual
time=1.562..2473.554 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Nested Loop (cost=0.00..2384.24 rows=638 width=8) (actual
time=0.747..2140.160 rows=61416 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.423..0.435 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Index Scan using b_r_device_index on batch_report_index b
(cost=0.00..2365.82 rows=1083 width=12) (actual time=0.288..1868.118
rows=61416 loops=1)
Index Cond: (b.device_id = "outer".device_id)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.359..0.359
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.198..0.237 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 2515.950 ms
(11 rows)
He then suggested I increase the statistics on batch_report_index & run
the query again. I "set statistics" for both
the device_id and technology_id column to 900, vacuum analyzed, and
re-ran the query (it's still slower than query
1 run after the same contortions ):
(query 2 explain analyze)
Hash Join (cost=1.18..1608.49 rows=46 width=4) (actual
time=1.437..1499.414 rows=61416 loops=1)
Hash Cond: ("outer".technology_id = "inner".technology_id)
-> Nested Loop (cost=0.00..1603.66 rows=638 width=8) (actual
time=0.613..1185.826 rows=61416 loops=1)
-> Index Scan using device_index_device_name_key on
device_index d (cost=0.00..4.88 rows=1 width=4) (actual
time=0.246..0.259 rows=1 loops=1)
Index Cond: (device_name = 'CP8M'::text)
-> Index Scan using b_r_device_index on batch_report_index b
(cost=0.00..1589.93 rows=708 width=12) (actual time=0.324..928.888
rows=61416 loops=1)
Index Cond: (b.device_id = "outer".device_id)
-> Hash (cost=1.18..1.18 rows=1 width=4) (actual time=0.358..0.358
rows=0 loops=1)
-> Seq Scan on technology_index t (cost=0.00..1.18 rows=1
width=4) (actual time=0.196..0.238 rows=1 loops=1)
Filter: (technology_name = 'G12'::text)
Total runtime: 1538.302 ms
At this point, he said "send it to the -perform mailing list". So here
I am.
The relevant table schemas as of right now:
reg=# \d batch_report_index
Table "public.batch_report_index"
Column | Type |
Modifiers
-----------------+------------------------
+---------------------------------------------------------------------
batch_report_id | integer | not null default
nextval('"batch_report__batch_report__seq"'::text)
lot | character varying(16) |
tool_id | integer | not null
technology_id | integer | not null
device_id | integer | not null
reticle_id | integer | not null
layer_id | integer | not null
layer_number | integer | not null
image_id | integer |
start_date | date |
start_time | time without time zone |
stop_date | date |
stop_time | time without time zone |
in_system | boolean | default false
Indexes:
"batch_report_index_pkey" primary key, btree (batch_report_id)
"b_r_device_index" btree (device_id)
"batch_report_stop_date_indexing" btree (stop_date)
"batch_report_tool_id_index" btree (tool_id)
reg=# \d technology_index
Table "public.technology_index"
Column | Type | Modifiers
-----------------+---------
+---------------------------------------------------------------------
technology_id | integer | not null default
nextval('"technology_in_technology_id_seq"'::text)
technology_name | text | not null
Indexes:
"technology_index_pkey" primary key, btree (technology_id)
"technology_in_technology_na_key" unique, btree (technology_name)
reg=# \d device_index
Table "public.device_index"
Column | Type | Modifiers
-------------+---------
+----------------------------------------------------------------
device_id | integer | not null default
nextval('"device_index_device_id_seq"'::text)
device_name | text | not null
Indexes:
"device_index_pkey" primary key, btree (device_id)
"device_index_device_name_key" unique, btree (device_name)
Thanks for the great work y'all do!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Join vs Subquery
@ 2007-05-03 06:07 Brian Herlihy <btherl@yahoo.com.au>
2007-05-03 11:46 ` Re: Join vs Subquery Gregory Stark <stark@enterprisedb.com>
2007-05-03 14:23 ` Re: Join vs Subquery Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Brian Herlihy @ 2007-05-03 06:07 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am using postgres 8.1.3 for this. If this has been dealt with later, please disregard. And this is not a complaint or a request, I am just curious, so I know how to best construct my queries.
There is a unique index mapping domains to domain_ids.
views_ts specifies a partitioned table, where views_ts_2007_04_01 is the only partition matching the range given in the query.
My goal is to produce summaries of counts of rows for each day within a given range (can be days, months, years).
The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query.
Thanks!
Brian
live=> explain select ts::date,count(*) from views_ts join domains using (domain_id) where domain = '1234.com' and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=9040.97..9041.00 rows=2 width=8)
-> Hash Join (cost=6.01..9040.96 rows=2 width=8)
Hash Cond: ("outer".domain_id = "inner".domain_id)
-> Append (cost=0.00..7738.01 rows=259383 width=16)
-> Seq Scan on views_ts (cost=0.00..1138.50 rows=1 width=16)
Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
-> Seq Scan on views_ts_2007_04_01 views_ts (cost=0.00..6599.51 rows=259382 width=16)
Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
-> Hash (cost=6.01..6.01 rows=1 width=8)
-> Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8)
Index Cond: ("domain" = '1234.com'::text)
(11 rows)
live=> explain select ts::date,count(*) from views_ts where domain_id = (select domain_id from domains where domain = '1234.com') and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1993.93..1995.99 rows=137 width=8)
InitPlan
-> Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8)
Index Cond: ("domain" = '1234.com'::text)
-> Result (cost=0.00..1986.69 rows=247 width=8)
-> Append (cost=0.00..1986.07 rows=247 width=8)
-> Seq Scan on views_ts (cost=0.00..1245.75 rows=1 width=8)
Filter: ((domain_id = $0) AND (ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
-> Bitmap Heap Scan on views_ts_2007_04_01 views_ts (cost=2.86..740.32 rows=246 width=8)
Recheck Cond: (domain_id = $0)
Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
-> Bitmap Index Scan on views_ts_2007_04_01_domain_id (cost=0.00..2.86 rows=246 width=0)
Index Cond: (domain_id = $0)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Join vs Subquery
2007-05-03 06:07 Join vs Subquery Brian Herlihy <btherl@yahoo.com.au>
@ 2007-05-03 11:46 ` Gregory Stark <stark@enterprisedb.com>
1 sibling, 0 replies; 4+ messages in thread
From: Gregory Stark @ 2007-05-03 11:46 UTC (permalink / raw)
To: Brian Herlihy <btherl@yahoo.com.au>; +Cc: pgsql-performance
"Brian Herlihy" <btherl@yahoo.com.au> writes:
> There is a unique index mapping domains to domain_ids.
...
> The issue: the second query results in a lower cost estimate. I am wondering
> why the second query plan was not chosen for the first query.
Well the unique index you mentioned is critical to being able to conclude the
queries are equivalent. Postgres in the past hasn't been able to use things
like unique indexes to make planning decisions because it had no
infrastructure to replan if you dropped the index. We do have such
infrastructure now so it may be possible to add features like this in the
future.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Join vs Subquery
2007-05-03 06:07 Join vs Subquery Brian Herlihy <btherl@yahoo.com.au>
@ 2007-05-03 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-05-03 14:23 UTC (permalink / raw)
To: Brian Herlihy <btherl@yahoo.com.au>; +Cc: pgsql-performance
Brian Herlihy <btherl@yahoo.com.au> writes:
> The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query.
8.1 is incapable of pushing indexable join conditions down below an Append.
Try 8.2.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-03 14:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 06:47 join vs. subquery David Haas <dave@modelpredictivesystems.com>
2007-05-03 06:07 Join vs Subquery Brian Herlihy <btherl@yahoo.com.au>
2007-05-03 11:46 ` Re: Join vs Subquery Gregory Stark <stark@enterprisedb.com>
2007-05-03 14:23 ` Re: Join vs Subquery Tom Lane <tgl@sss.pgh.pa.us>
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