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

From: "Klaus P(dot) Pieper" <kpi6288(at)gmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: LIMIT clause slowing down query in some cases, accelerating in others
Date: 2017-04-26 18:11:50
Message-ID: 012701d2beb8$93ef5040$bbcdf0c0$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message basti 2017-04-27 10:18:56 Postgres connection Monitor
Previous Message Joshua D. Drake 2017-04-26 16:09:30 Re: TimeScaleDB -- Open Source Time Series Database Released (www.i-programmer.info);