Re: Terribly slow query with very good plan?

[Les <nagylzs@xxxxxxxxx>]

 

> PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan for A, I, C letters (with the "like" query).

That's interesting.

Does it help if you create an additional index on relpath with the
text_pattern_ops modifier, e.g.

CREATE INDEX ... USING btree (relpath text_pattern_ops);

It does not help. Details below. (PostgreSQL version 12.8)

CREATE index test ON media.oo_file (relpath COLLATE "C");
CREATE INDEX test2 ON media.oo_file USING btree (relpath text_pattern_ops);
CREATE INDEX test3 ON media.oo_file USING btree (relpath collate "C" text_pattern_ops);

-- letter "A" ^@ operator -> slow seq scan

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active  and fi.relpath ^@ 'A' limit 1;

QUERY PLAN                                                                                                                  |
----------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=0.00..1904.09 rows=1 width=8) (actual time=10779.585..10779.587 rows=0 loops=1)                                |
  Output: id                                                                                                                |
  Buffers: shared hit=9960 read=121303                                                                                      |
  ->  Seq Scan on media.oo_file fi  (cost=0.00..144710.65 rows=76 width=8) (actual time=10779.582..10779.583 rows=0 loops=1)|
        Output: id                                                                                                          |
        Filter: (fi.is_active AND (fi.relpath ^@ 'A'::text))                                                                |
        Rows Removed by Filter: 1075812                                                                                     |
        Buffers: shared hit=9960 read=121303                                                                                |
Planning Time: 0.428 ms                                                                                                     |
Execution Time: 10779.613 ms                                                                                                |

-- letter 'A' like _expression_ index scan fast

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active  and fi.relpath like 'A%' limit 1;

QUERY PLAN                                                                                                                     |
-------------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=0.55..60.85 rows=1 width=8) (actual time=7.047..7.048 rows=0 loops=1)                                             |
  Output: id                                                                                                                   |
  Buffers: shared hit=2776                                                                                                     |
  ->  Index Scan using test on media.oo_file fi  (cost=0.55..4583.29 rows=76 width=8) (actual time=7.045..7.045 rows=0 loops=1)|
        Output: id                                                                                                             |
        Index Cond: ((fi.relpath >= 'A'::text) AND (fi.relpath < 'B'::text))                                                   |
        Filter: (fi.is_active AND (fi.relpath ~~ 'A%'::text))                                                                  |
        Rows Removed by Filter: 3784                                                                                           |
        Buffers: shared hit=2776                                                                                               |
Planning Time: 0.937 ms                                                                                                        |
Execution Time: 7.091 ms                                                                                                       |

-- letter 'T' like _expression_, seq scan slow

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active  and fi.relpath like 'Természettudomány%' limit 1;

QUERY PLAN                                                                                                                   |
-----------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=0.00..2.70 rows=1 width=8) (actual time=9842.935..9842.938 rows=0 loops=1)                                      |
  Output: id                                                                                                                 |
  Buffers: shared hit=10024 read=121239                                                                                      |
  ->  Seq Scan on media.oo_file fi  (cost=0.00..144710.65 rows=53574 width=8) (actual time=9842.933..9842.934 rows=0 loops=1)|
        Output: id                                                                                                           |
        Filter: (fi.is_active AND (fi.relpath ~~ 'Természettudomány%'::text))                                                |
        Rows Removed by Filter: 1075812                                                                                      |
        Buffers: shared hit=10024 read=121239                                                                                |
Planning Time: 0.975 ms                                                                                                      |
Execution Time: 9842.962 ms                                                                                                  |