LIMIT clause slowing down query in some cases, accelerating in others

Started by Klaus P. Pieperalmost 9 years ago2 messagesgeneral
Jump to latest
#1Klaus P. Pieper
kpi6288@gmail.com

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?

#2Vik Fearing
vik@postgresfriends.org
In reply to: Klaus P. Pieper (#1)
Re: LIMIT clause slowing down query in some cases, accelerating in others

On 04/26/2017 08:11 PM, Klaus P. Pieper wrote:

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