pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTwo fast queries get slow when combined
5+ messages / 3 participants
[nested] [flat]
* Two fast queries get slow when combined
@ 2007-10-30 20:48 cluster <skrald@amossen.dk>
2007-10-30 21:46 ` Re: Two fast queries get slow when combined Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-31 04:05 ` Re: Two fast queries get slow when combined Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: cluster @ 2007-10-30 20:48 UTC (permalink / raw)
To: pgsql-performance
I have two small queries which are both very fast to evaluate
separately. The first query, "Query 1", calculates some statistics and
the the second query, "Query 2", finds a subset of relevant keys.
When combined into a single query which calculates statistics from only
the subset of relevant keys the evaluation plan explodes and uses both
seq scans and bitmap heap scans.
How can I improve the performance of the combined query?
Queries and output from EXPLAIN ANALYZE can be seen here with some
syntax highlighting:
http://rafb.net/p/BJIW4p69.html
I will also paste it here:
=============================================================================
QUERY 1 (very *fast*):
=============================================================================
SELECT keyId, count(1) as num_matches
FROM stats
GROUP BY keyId
LIMIT 50
Limit (cost=0.00..23.65 rows=50 width=8) (actual time=0.090..2.312
rows=50 loops=1)
-> GroupAggregate (cost=0.00..4687.46 rows=9912 width=8) (actual
time=0.085..2.145 rows=50 loops=1)
-> Index Scan using stats_keyId on stats (cost=0.00..3820.19
rows=99116 width=8) (actual time=0.031..1.016 rows=481 loops=1)
Total runtime: 2.451 ms
(4 rows)
=============================================================================
QUERY 2 (very *fast*):
=============================================================================
SELECT keyId, sortNum
FROM items i
WHERE sortNum > 123
ORDER BY sortNum
LIMIT 50
Limit (cost=0.01..9.87 rows=50 width=8) (actual time=0.068..0.610
rows=50 loops=1)
InitPlan
-> Limit (cost=0.00..0.01 rows=1 width=0) (actual
time=0.009..0.025 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.006..0.007 rows=1 loops=1)
-> Index Scan using items_sortNum on items i (cost=0.00..1053.67
rows=5344 width=8) (actual time=0.063..0.455 rows=50 loops=1)
Index Cond: (sortNum >= $0)
Total runtime: 0.749 ms
(7 rows)
=============================================================================
COMBINED QUERY (very *slow*):
=============================================================================
SELECT keyId, sortNum, count(1)
FROM stats s, items i
WHERE s.keyId = i.keyId AND i.sortNum > 123
GROUP BY i.keyId, i.sortNum
ORDER BY i.sortNum
LIMIT 50
Limit (cost=3281.72..3281.84 rows=50 width=16) (actual
time=435.838..436.043 rows=50 loops=1)
InitPlan
-> Limit (cost=0.00..0.01 rows=1 width=0) (actual
time=0.016..0.021 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.012..0.013 rows=1 loops=1)
-> Sort (cost=3281.71..3289.97 rows=3304 width=16) (actual
time=435.833..435.897 rows=50 loops=1)
Sort Key: i.sortNum
-> Hash Join (cost=2745.80..3088.59 rows=3304 width=16)
(actual time=364.247..413.164 rows=8490 loops=1)
Hash Cond: (s.keyId = i.keyId)
-> HashAggregate (cost=2270.53..2394.43 rows=9912
width=8) (actual time=337.869..356.533 rows=9911 loops=1)
-> Seq Scan on items (cost=0.00..1527.16
rows=99116 width=8) (actual time=0.016..148.118 rows=99116 loops=1)
-> Hash (cost=408.47..408.47 rows=5344 width=12)
(actual time=26.342..26.342 rows=4491 loops=1)
-> Bitmap Heap Scan on items i
(cost=121.67..408.47 rows=5344 width=12) (actual time=5.007..16.898
rows=4491 loops=1)
Recheck Cond: (sortNum >= $0)
-> Bitmap Index Scan on items_sortNum
(cost=0.00..120.33 rows=5344 width=0) (actual time=4.273..4.273
rows=13375 loops=1)
Index Cond: (sortNum >= $0)
Total runtime: 436.421 ms
(16 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two fast queries get slow when combined
2007-10-30 20:48 Two fast queries get slow when combined cluster <skrald@amossen.dk>
@ 2007-10-30 21:46 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-31 09:37 ` Re: Two fast queries get slow when combined cluster <skrald@amossen.dk>
1 sibling, 1 reply; 5+ messages in thread
From: Heikki Linnakangas @ 2007-10-30 21:46 UTC (permalink / raw)
To: cluster <skrald@amossen.dk>; +Cc: pgsql-performance
cluster wrote:
> SELECT keyId, sortNum, count(1)
> FROM stats s, items i
> WHERE s.keyId = i.keyId AND i.sortNum > 123
> GROUP BY i.keyId, i.sortNum
> ORDER BY i.sortNum
> LIMIT 50
>
> Limit (cost=3281.72..3281.84 rows=50 width=16) (actual
> time=435.838..436.043 rows=50 loops=1)
> InitPlan
> -> Limit (cost=0.00..0.01 rows=1 width=0) (actual
> time=0.016..0.021 rows=1 loops=1)
> -> Result (cost=0.00..0.01 rows=1 width=0) (actual
> time=0.012..0.013 rows=1 loops=1)
> -> Sort (cost=3281.71..3289.97 rows=3304 width=16) (actual
> time=435.833..435.897 rows=50 loops=1)
> Sort Key: i.sortNum
> -> Hash Join (cost=2745.80..3088.59 rows=3304 width=16)
> (actual time=364.247..413.164 rows=8490 loops=1)
> Hash Cond: (s.keyId = i.keyId)
> -> HashAggregate (cost=2270.53..2394.43 rows=9912
> width=8) (actual time=337.869..356.533 rows=9911 loops=1)
> -> Seq Scan on items (cost=0.00..1527.16
> rows=99116 width=8) (actual time=0.016..148.118 rows=99116 loops=1)
> -> Hash (cost=408.47..408.47 rows=5344 width=12)
> (actual time=26.342..26.342 rows=4491 loops=1)
> -> Bitmap Heap Scan on items i
> (cost=121.67..408.47 rows=5344 width=12) (actual time=5.007..16.898
> rows=4491 loops=1)
> Recheck Cond: (sortNum >= $0)
> -> Bitmap Index Scan on items_sortNum
> (cost=0.00..120.33 rows=5344 width=0) (actual time=4.273..4.273
> rows=13375 loops=1)
> Index Cond: (sortNum >= $0)
> Total runtime: 436.421 ms
> (16 rows)
There's something odd about that plan. It's doing both a seq scan and a
bitmap scan on "items", but I can't see stats table being mentioned
anywhere. Looking at the row count, I believe that seq scan is actually
on the stats table, not items like it says above. Is that really a
verbatim copy of the output you got?
Which version of Postgres is this?
You could try rewriting the query like this:
SELECT keyId, sortNum,
(SELECT count(*) FROM stats s WHERE s.keyId = i.keyId) AS stats_cnt
FROM items i
WHERE i.sortNum > 123
ORDER BY sortNum
LIMIT 50
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two fast queries get slow when combined
2007-10-30 20:48 Two fast queries get slow when combined cluster <skrald@amossen.dk>
2007-10-30 21:46 ` Re: Two fast queries get slow when combined Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-10-31 09:37 ` cluster <skrald@amossen.dk>
0 siblings, 0 replies; 5+ messages in thread
From: cluster @ 2007-10-31 09:37 UTC (permalink / raw)
To: pgsql-performance
> There's something odd about that plan. It's doing both a seq scan and a
> bitmap scan on "items", but I can't see stats table being mentioned
> anywhere.
Huh? Aaah, sorry. I made a major search/replace-refactoring (that
obviously went wrong) on all open files in the editor before posting to
this newsgroup, and one of these files was my preparation of the queries
and planner output. Damn.
Sorry for wasting your time! :-(
However, your suggestion worked perfectly. Thanks a lot!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two fast queries get slow when combined
2007-10-30 20:48 Two fast queries get slow when combined cluster <skrald@amossen.dk>
@ 2007-10-31 04:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-31 09:39 ` Re: Two fast queries get slow when combined cluster <skrald@amossen.dk>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-10-31 04:05 UTC (permalink / raw)
To: cluster <skrald@amossen.dk>; +Cc: pgsql-performance
cluster <skrald@amossen.dk> writes:
> Queries and output from EXPLAIN ANALYZE can be seen here with some
> syntax highlighting:
> http://rafb.net/p/BJIW4p69.html
You are lying to us about how those queries were posed to Postgres
(and no I don't feel a need to explain how I know). In future please
present the full truth about what you are doing, not a simplification
that you think is sufficient.
But I think the short answer to your question is that query 1 is fast
because it need only select the first 50 rows in some ordering, and
query 2 is fast because it need only select the first 50 rows in
some ordering, but they are not the same ordering so the join query
doesn't get to exploit that shortcut.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two fast queries get slow when combined
2007-10-30 20:48 Two fast queries get slow when combined cluster <skrald@amossen.dk>
2007-10-31 04:05 ` Re: Two fast queries get slow when combined Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-31 09:39 ` cluster <skrald@amossen.dk>
0 siblings, 0 replies; 5+ messages in thread
From: cluster @ 2007-10-31 09:39 UTC (permalink / raw)
To: pgsql-performance
> You are lying to us about how those queries were posed to Postgres
> (and no I don't feel a need to explain how I know).
Sorry. The "lying" was not intended as explained in my reply to Heikku.
Thanks for the tips anyways.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-31 09:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-30 20:48 Two fast queries get slow when combined cluster <skrald@amossen.dk>
2007-10-30 21:46 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-31 09:37 ` cluster <skrald@amossen.dk>
2007-10-31 04:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-31 09:39 ` cluster <skrald@amossen.dk>
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