pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAn unwanted seqscan
3+ messages / 2 participants
[nested] [flat]
* An unwanted seqscan
@ 2007-02-14 08:40 Brian Herlihy <btherl@yahoo.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Brian Herlihy @ 2007-02-14 08:40 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am having trouble understanding why a seqscan is chosen for this query.
In practice the seqscan is very expensive, whereas the nested loop is usually quite fast, even with several hundred rows returned from meta_keywords_url.
The server is running version 8.1.3, and both tables were analyzed recently. meta_keywords contains around 25% dead rows, meta_keywords_url contains no dead rows.
I have included the query written both as a subquery and as a join.
Thanks for any assistance!
Brian
live=> explain select * from meta_keywords where url_id in (select url_id from meta_keywords_url where host = 'postgresql.org');
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Hash IN Join (cost=1755.79..545380.52 rows=9442 width=29)
Hash Cond: ("outer".url_id = "inner".url_id)
-> Seq Scan on meta_keywords (cost=0.00..507976.54 rows=7110754 width=29)
-> Hash (cost=1754.35..1754.35 rows=576 width=4)
-> Bitmap Heap Scan on meta_keywords_url (cost=11.02..1754.35 rows=576 width=4)
Recheck Cond: ((host)::text = 'postgresql.org'::text)
-> Bitmap Index Scan on meta_keywords_url_host_path (cost=0.00..11.02 rows=576 width=0)
Index Cond: ((host)::text = 'postgresql.org'::text)
(8 rows)
live=> set enable_seqscan=off;
SET
live=> explain select * from meta_keywords where url_id in (select url_id from meta_keywords_url where host = 'postgresql.org');
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Nested Loop (cost=1755.79..3161748.83 rows=9442 width=29)
-> HashAggregate (cost=1755.79..1761.55 rows=576 width=4)
-> Bitmap Heap Scan on meta_keywords_url (cost=11.02..1754.35 rows=576 width=4)
Recheck Cond: ((host)::text = 'postgresql.org'::text)
-> Bitmap Index Scan on meta_keywords_url_host_path (cost=0.00..11.02 rows=576 width=0)
Index Cond: ((host)::text = 'postgresql.org'::text)
-> Index Scan using meta_keywords_url_id on meta_keywords (cost=0.00..5453.28 rows=2625 width=29)
Index Cond: (meta_keywords.url_id = "outer".url_id)
(8 rows)
live=> explain select * from meta_keywords join meta_keywords_url using (url_id) where host = 'postgresql.org'; QUERY PLAN
---------------------------------------------------------------------------------------------------------
Hash Join (cost=1758.52..543685.43 rows=9297 width=107)
Hash Cond: ("outer".url_id = "inner".url_id)
-> Seq Scan on meta_keywords (cost=0.00..506859.29 rows=6994929 width=28)
-> Hash (cost=1757.08..1757.08 rows=577 width=83)
-> Bitmap Heap Scan on meta_keywords_url (cost=11.02..1757.08 rows=577 width=83)
Recheck Cond: ((host)::text = 'postgresql.org'::text)
-> Bitmap Index Scan on meta_keywords_url_host_path (cost=0.00..11.02 rows=577 width=0)
Index Cond: ((host)::text = 'postgresql.org'::text)
(8 rows)
live=> set enable_seqscan=off;
SET
live=> explain select * from meta_keywords join meta_keywords_url using (url_id) where host = 'postgresql.org';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3348211.21 rows=9297 width=107)
-> Index Scan using meta_keywords_url_host_path on meta_keywords_url (cost=0.00..2230.24 rows=577 width=83)
Index Cond: ((host)::text = 'postgresql.org'::text)
-> Index Scan using meta_keywords_url_id on meta_keywords (cost=0.00..5765.81 rows=2649 width=28)
Index Cond: (meta_keywords.url_id = "outer".url_id)
(5 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: An unwanted seqscan
@ 2007-02-14 08:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian Herlihy <btherl@yahoo.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-02-14 08:53 UTC (permalink / raw)
To: Brian Herlihy <btherl@yahoo.com.au>; +Cc: pgsql-performance
Brian Herlihy <btherl@yahoo.com.au> writes:
> I am having trouble understanding why a seqscan is chosen for this query.
As far as anyone can see from this output, the planner's decisions are
correct: it prefers the plans with the smaller estimated cost. If you
want us to take an interest, provide some more context --- EXPLAIN
ANALYZE output for starters.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: An unwanted seqscan
@ 2007-02-15 01:30 Brian Herlihy <btherl@yahoo.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Brian Herlihy @ 2007-02-15 01:30 UTC (permalink / raw)
To: pgsql-performance
Hi Tom,
Sorry, I didn't ask the right question. I meant to ask "Why does it estimate a smaller cost for the seqscan?"
With some further staring I was able to find the bad estimate and fix it by increasing the relevant statistics target.
Thanks,
Brian
----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Brian Herlihy <btherl@yahoo.com.au>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Sent: Wednesday, 14 February, 2007 4:53:54 PM
Subject: Re: [PERFORM] An unwanted seqscan
Brian Herlihy <btherl@yahoo.com.au> writes:
> I am having trouble understanding why a seqscan is chosen for this query.
As far as anyone can see from this output, the planner's decisions are
correct: it prefers the plans with the smaller estimated cost. If you
want us to take an interest, provide some more context --- EXPLAIN
ANALYZE output for starters.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-15 01:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-14 08:40 An unwanted seqscan Brian Herlihy <btherl@yahoo.com.au>
2007-02-14 08:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-15 01:30 Re: An unwanted seqscan Brian Herlihy <btherl@yahoo.com.au>
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