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 CAKXe9UDkX0ff-a_qW87UEbsZLKbULcsfj9R_EMBtr+hcGhEZtQ@mail.gmail.com
Whole thread Raw
In response to Re: Terribly slow query with very good plan?  (Nick Cleaton <nick@cleaton.net>)
List pgsql-performance
Tree view


That may be because it's expecting to get 88290 rows from the
sequential scan, and the"limit 1" means it expects sequential scan to
be fast because on average it will only need to scan 1/88290 of the
table before it finds a matching row, then it can stop.


We are looking for a single row. With an index scan, it is always much faster to find a single row. No seq scan can be faster "on average", when you are looking for a single row. Am I wrong?

> Try it without the "limit 1"


Without the limit it uses bitmap heap scan. Unbelievable!

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select fi.id from media.oo_file fi
where fi.is_active  
and fi.relpath between
('Természettudomány' collate "C")
and ('Természettudomány'||chr(255) collate "C");

QUERY PLAN                                                                                                                            |
--------------------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on media.oo_file fi  (cost=10480.10..140065.96 rows=70010 width=8) (actual time=9757.917..9757.920 rows=0 loops=1)   |
  Output: id                                                                                                                          |
  Recheck Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C"))    |
  Filter: fi.is_active                                                                                                                |
  Rows Removed by Filter: 85207                                                                                                       |
  Heap Blocks: exact=24954                                                                                                            |
  Buffers: shared hit=197 read=26531                                                                                                  |
  ->  Bitmap Index Scan on test  (cost=0.00..10462.59 rows=99404 width=0) (actual time=425.571..425.572 rows=85207 loops=1)           |
        Index Cond: ((fi.relpath >= 'Természettudomány'::text COLLATE "C") AND (fi.relpath <= 'Természettudomány '::text COLLATE "C"))|
        Buffers: shared hit=6 read=1768                                                                                               |
Planning Time: 1.145 ms                                                                                                               |
JIT:                                                                                                                                  |
  Functions: 6                                                                                                                        |
  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                       |
  Timing: Generation 2.295 ms, Inlining 0.000 ms, Optimization 1.142 ms, Emission 11.632 ms, Total 15.070 ms                          |
Execution Time: 9760.361 ms                                                                                                           |



 

pgsql-performance by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: Terribly slow query with very good plan?
Next
From: Vijaykumar Jain
Date:
Subject: Re: Terribly slow query with very good plan?