Re: Terribly slow query with very good plan?

From: Les <nagylzs(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: Terribly slow query with very good plan?
Date: 2022-02-04 10:00:47
Message-ID: CAKXe9UAaxcw=r8BKa2KXkkee_aUMM8qH89GtELTpVqNR+ee5HQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I really think now that the query plan is wrong (or "could be improved" so
to say). As far as I understand, the "index only scan" is essentially a
sequential scan on the index data. In this specific case, where the filter
is a "begins with" condition on a field that is the starting (and only)
column of an index, there is a much much better way to find out if there is
a row or not: lookup the closest value in the index and see if it begins
with the value. The operation of looking up the closest value in an index
would be much more efficient.

> I don't understand how it is possible in the slow case Rows Removed by
Filter: 792025 (returns 0 row) and in the second case Rows Removed by
Filter: 15 (returns 1 row).

Pavel, I think it is because the scan found a suitable row at the beginning
of the scan and stopped the scan. If you look at that plan you will see
that it uses a seq scan. It was fast by accident. :-)

The plan of that single-row version was changed to a normal index scan,
after I added the collation "C" index:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select exists (
select id from media.oo_file of2 where relpath like 'this does not exist%'
);
QUERY PLAN
|
-------------------------------------------------------------------------------------------------------------------------------------+
Result (cost=0.63..0.64 rows=1 width=1) (actual time=0.022..0.023 rows=1
loops=1) |
Output: $0
|
Buffers: shared hit=4
|
InitPlan 1 (returns $0)
|
-> Index Only Scan using test on media.oo_file of2 (cost=0.55..8.57
rows=108 width=0) (actual time=0.018..0.018 rows=0 loops=1)|
Index Cond: ((of2.relpath >= 'this does not exist'::text) AND
(of2.relpath < 'this does not exisu'::text)) |
Filter: (of2.relpath ~~ 'this does not exist%'::text)
|
Heap Fetches: 0
|
Buffers: shared hit=4
|
Planning Time: 0.530 ms
|
Execution Time: 0.055 ms
|

I would expect for the same originally slow query with the has_file column,
but it does not happen. :-(

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Les 2022-02-04 10:05:13 Re: Terribly slow query with very good plan?
Previous Message Nick Cleaton 2022-02-04 09:59:55 Re: Terribly slow query with very good plan?