limit 1 on view never finishes

From: Craig James <cjames(at)emolecules(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: limit 1 on view never finishes
Date: 2016-10-27 20:46:05
Message-ID: CAFwQ8rcOD1J+FnaohjY7UcLsUUpiDkCROKFCZpcMcUXKuic0+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Filip Rembiałkowski 2016-10-28 01:37:19 Re: query slowdown after 9.0 -> 9.4 migration
Previous Message Andreas Kretschmer 2016-10-27 05:38:38 Re: query slowdown after 9.0 -> 9.4 migration