Postgres Pro
Downloads
Home   >   mailing lists

Re: Terribly slow query with very good plan? - Mailing list pgsql-performance

From Les
Subject Re: Terribly slow query with very good plan?
Date
Msg-id CAKXe9UB0M=O3SJcwMU_FE-6xM_pBpPeCWXeL4S7y3yTN7+MqUw@mail.gmail.com
Whole thread Raw
In response to Re: Terribly slow query with very good plan?  (Nick Cleaton <nick@cleaton.net>)
Responses Re: Terribly slow query with very good plan?  (Ninad Shah <ninad.shah@percona.com>)
Re: Terribly slow query with very good plan?  (Nick Cleaton <nick@cleaton.net>)
List pgsql-performance
Tree view

>
> It does not help.

What if you try applying the C collation to the values from the table:

where fi.is_active  and fi.relpath collate "C" ^@ 'A'

Slow

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active  and fi.relpath collate "C" ^@ 'A' limit 1;
QUERY PLAN                                                                                                                |
--------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=0.00..1904.09 rows=1 width=8) (actual time=3837.338..3837.340 rows=0 loops=1)                                |
  Output: id                                                                                                              |
  Buffers: shared hit=9355 read=121908                                                                                    |
  ->  Seq Scan on media.oo_file fi  (cost=0.00..144710.65 rows=76 width=8) (actual time=3837.336..3837.336 rows=0 loops=1)|
        Output: id                                                                                                        |
        Filter: (fi.is_active AND ((fi.relpath)::text ^@ 'A'::text))                                                      |
        Rows Removed by Filter: 1075812                                                                                   |
        Buffers: shared hit=9355 read=121908                                                                              |
Planning Time: 0.391 ms                                                                                                   |
Execution Time: 3837.364 ms                                                                                               |

pgsql-performance by date:

Previous
From: Nick Cleaton
Date:
Subject: Re: Terribly slow query with very good plan?
Next
From: Ninad Shah
Date:
Subject: Re: Terribly slow query with very good plan?