Обсуждение: [GENERAL] LIMIT clause slowing down query in some cases, accelerating in others
[GENERAL] LIMIT clause slowing down query in some cases, accelerating in others
Running PostgreSQL 9.6 on a Windows Server.
Table “t” is kind of a materialized view with > 100 columns and 2.24 Mio rows. Queries are generated by an ORM framework – fairly difficult to modify.
Vacuum analyze was carried out – no impact.
The framework generates queries like this:
select N0."uorderid" from "t" N0
where (N0."szzip" like E'33%')
order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
EXPLAIN ANALYZE:
Limit (cost=0.43..547.08 rows=128 width=21) (actual time=402.247..402.386 rows=128 loops=1)
-> Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=19641 width=21) (actual time=402.244..402.344 rows=128 loops=1)
Filter: ((szzip)::text ~~ '33%'::text)
Rows Removed by Filter: 699108
Heap Fetches: 0
Planning time: 0.687 ms
Execution time: 402.443 ms
EXPLAIN ANALYZE without LIMIT and OFFSET:
Sort (cost=66503.14..66552.24 rows=19641 width=21) (actual time=151.598..156.155 rows=24189 loops=1)
Sort Key: szzip
Sort Method: quicksort Memory: 2658kB
-> Bitmap Heap Scan on t n0 (cost=200.22..65102.58 rows=19641 width=21) (actual time=21.267..90.272 rows=24189 loops=1)
Recheck Cond: ((szzip)::text ~~ '33%'::text)
Rows Removed by Index Recheck: 26
Heap Blocks: exact=23224
-> Bitmap Index Scan on t_szzip_idx_gin (cost=0.00..195.31 rows=19641 width=0) (actual time=14.235..14.235 rows=24215 loops=1)
Index Cond: ((szzip)::text ~~ '33%'::text)
Planning time: 0.669 ms
Execution time: 161.860 ms
With LIMIT, a btree index is used whereas without the LIMIT clause, a GIN index is used.
Unfortunately, modifying the LIKE query parameter from E'33%' to E'10%' gives completely different results:
select N0."uorderid" from "t" N0
where (N0."szzip" like E'10%')
order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
EXPLAIN ANALYZE:
Limit (cost=0.43..195.08 rows=128 width=21) (actual time=88.699..88.839 rows=128 loops=1)
-> Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=55158 width=21) (actual time=88.696..88.793 rows=128 loops=1)
Filter: ((szzip)::text ~~ '10%'::text)
Rows Removed by Filter: 142107
Heap Fetches: 0
Planning time: 0.669 ms
Execution time: 88.900 ms
EXPLAIN ANALYZE without LIMIT and OFFSET:
Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=55158 width=21) (actual time=88.483..1263.396 rows=53872 loops=1)
Filter: ((szzip)::text ~~ '10%'::text)
Rows Removed by Filter: 2192769
Heap Fetches: 0
Planning time: 0.671 ms
Execution time: 1274.761 ms
In this case, the GIN index is not used at all.
Anything else I can do about this?
Re: [GENERAL] LIMIT clause slowing down query in some cases,accelerating in others
Running PostgreSQL 9.6 on a Windows Server.
Table “t” is kind of a materialized view with > 100 columns and 2.24 Mio rows. Queries are generated by an ORM framework – fairly difficult to modify.
Vacuum analyze was carried out – no impact.
The framework generates queries like this:
select N0."uorderid" from "t" N0
where (N0."szzip" like E'33%')
order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
The planner has to choose whether to use an index for filtering or an index for sorting. If you're always doing prefix searches like in your two examples, then you want an index which can do both.
CREATE INDEX ON t (szzip text_pattern_ops, uorderid);
I invite you to read the documentation about text_pattern_ops at https://www.postgresql.org/docs/current/static/indexes-opclass.html
-- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support