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? |
