limit 1 on view never finishes
| От | Craig James |
|---|---|
| Тема | limit 1 on view never finishes |
| Дата | |
| Msg-id | CAFwQ8rcOD1J+FnaohjY7UcLsUUpiDkCROKFCZpcMcUXKuic0+A@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: limit 1 on view never finishes
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| Список | 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+ versionView "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.createdFROM registry.version rvJOIN ( SELECT DISTINCT sample.version_idFROM 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
В списке pgsql-performance по дате отправления: