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?