pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlimit 1 on view never finishes
2+ messages / 2 participants
[nested] [flat]
* limit 1 on view never finishes
@ 2016-10-27 20:46 Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 2+ messages in thread
From: Craig James @ 2016-10-27 20:46 UTC (permalink / raw)
To: pgsql-performance
This is a weird problem. A "limit 5" query runs quicky as expected, but a
"limit 1" query never finishes -- it just blasts along at 100% CPU until I
give up. And this is a join between two small tables (262K rows and 109K
rows). Both tables were recently analyzed.
This is Postgres 9.3.5 (yes, we'll be upgrading soon...), running on Ubuntu
12.04.
Note that "version" is a view in the schema being queried, but
registry.version is a real table. The idea is that the view in the schema
being queried is a filter that narrows the "registry.version" table to only
rows relevant to this schema.
s=> \d+ version
View "chemdiv_bb.version"
Column | Type | Modifiers | Storage |
Description
------------+-----------------------------+-----------+----------+-------------
version_id | integer | | plain |
parent_id | integer | | plain |
isosmiles | text | | extended |
created | timestamp without time zone | | plain |
View definition:
SELECT rv.version_id,
rv.parent_id,
rv.isosmiles,
rv.created
FROM registry.version rv
JOIN ( SELECT DISTINCT sample.version_id
FROM sample) ss USING (version_id);
The column "version_id" is indexed on both tables (it's PK on the
registry.version table).
explain select version_id from version order by version_id desc limit 5;
Limit (cost=14577.29..14577.70 rows=5 width=4) (actual
time=1077.113..1077.162 rows=5 loops=1)
-> Merge Join (cost=14577.29..23681.16 rows=109114 width=4) (actual
time=1077.108..1077.142 rows=5 loops=1)
Merge Cond: (rv.version_id = sample.version_id)
-> Index Only Scan Backward using version_pkey on version rv
(cost=0.42..6812.85 rows=261895 width=4) (actual time=0.045..126.641
rows=70125 loops=1)
Heap Fetches: 0
-> Sort (cost=14576.87..14849.65 rows=109114 width=4) (actual
time=830.842..830.851 rows=5 loops=1)
Sort Key: sample.version_id
Sort Method: quicksort Memory: 8188kB
-> HashAggregate (cost=3264.21..4355.35 rows=109114
width=4) (actual time=420.018..630.393 rows=109133 loops=1)
-> Seq Scan on sample (cost=0.00..2991.37
rows=109137 width=4) (actual time=0.012..206.822 rows=109137 loops=1)
Total runtime: 1078.363 ms
No problem, works as expected. But lower the limit to 1 and it never
finishes. I can't show "explain analyze ...", so here's the output from
just "explain".
explain select version_id from version order by version_id desc limit 1;
Limit (cost=3264.63..7193.14 rows=1 width=4)
-> Nested Loop (cost=3264.63..428658697.57 rows=109114 width=4)
Join Filter: (rv.version_id = sample.version_id)
-> Index Only Scan Backward using version_pkey on version rv
(cost=0.42..6812.85 rows=261895 width=4)
-> Materialize (cost=3264.21..5992.06 rows=109114 width=4)
-> HashAggregate (cost=3264.21..4355.35 rows=109114
width=4)
-> Seq Scan on sample (cost=0.00..2991.37
rows=109137 width=4)
Why would this trivial query run forever at 100% CPU?
This, by the way, is the "old fashioned" way to do max(version_id), which
used to be slow in Postgres. I have switched the query to use
max(version_id), but worry that other queries will get hung up for no
apparent reason.
Thanks,
Craig
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: limit 1 on view never finishes
@ 2016-11-02 23:37 Jim Nasby <Jim.Nasby@BlueTreble.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Nasby @ 2016-11-02 23:37 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; pgsql-performance
On 10/27/16 3:46 PM, Craig James wrote:
>
> Limit (cost=3264.63..7193.14 rows=1 width=4)
> -> Nested Loop (cost=3264.63..428658697.57 rows=109114 width=4)
> Join Filter: (rv.version_id = sample.version_id)
> -> Index Only Scan Backward using version_pkey on version rv
> (cost=0.42..6812.85 rows=261895 width=4)
> -> Materialize (cost=3264.21..5992.06 rows=109114 width=4)
> -> HashAggregate (cost=3264.21..4355.35 rows=109114
> width=4)
> -> Seq Scan on sample (cost=0.00..2991.37
> rows=109137 width=4)
>
> Why would this trivial query run forever at 100% CPU?
My bet is that there's a lot of rows in version that have a higher
version than what's in sample. That means a lot of repeated scans
through the tuplestore underneath the Materialize node.
If you can remove duplicates from sample and get rid of the DISTINCT,
this will probably get a better plan. If you can't do that then you
could try changing the JOIN to an IN:
SELECT ... FROM sample
WHERE version_id IN (SELECT version_id FROM sample)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-11-02 23:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-27 20:46 limit 1 on view never finishes Craig James <cjames@emolecules.com>
2016-11-02 23:37 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
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