Re: PG Query Planner

Hello,

My query is :

SELECT subs.id AS id1_109,
scriber_id AS subs_109
FROM subscription subs
LEFT OUTER JOIN offer offer1
ON subs.offer_id = offer1.id
WHERE offer1.is_external_lifecycle_management = FALSE
AND subs.job_next_process_time < '2022-04-19 09:25:25.535'
AND subs.job_in_progress = FALSE
ORDER BY subs.id ASC LIMIT 1 ;

Gaurav Anand <gaurav(dot)anand(at)saama(dot)com>, 19 Nis 2022 Sal, 20:45 tarihinde
şunu yazdı:

> Looks like your Index has gone wrong, instead of 92355 records it is
> scanning 8021769 rows which is why it is taking 6s.
>
> Share the sql too
>
> On Tue, 19 Apr 2022 at 11:07 PM, Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
> wrote:
>
>> Hello,
>>
>> I wonder how the query planner works in postgresql. So, I have a query
>> that takes 6 seconds with an index scan. However, the same query takes
>> 0.1ms when I set disable index scan parameter.
>>
>> How do I get the planner to make the right decision? Also, I ran ANALYZE
>> command many times.
>>
>> And I have an index on job_next_process_time column but did not use it
>> by the planner.
>>
>> Limit (cost=0.56..29.04 rows=1 width=695) (actual
>> time=6386.751..6386.753 rows=0 loops=1)
>> -> Nested Loop (cost=0.56..692987.46 rows=24331 width=695) (actual
>> time=6386.749..6386.751 rows=0 loops=1)
>> Join Filter: (subs.offer_id = offer1.id)
>> -> Index Scan using subs_pkey on subscription subs
>> (cost=0.56..572151.65 rows=8053633 width=695) (actual time=0.008..5554.872
>> rows=8021769 loops=1)
>> Filter: ((NOT job_in_progress) AND (job_next_process_time
>> < '2022-04-19 09:25:25.535'::timestamp without time zone))
>> Rows Removed by Filter: 72039
>> -> Materialize (cost=0.00..31.31 rows=1 width=8) (actual
>> time=0.000..0.000 rows=0 loops=8021769)
>> -> Seq Scan on offer offer1 (cost=0.00..31.31 rows=1
>> width=8) (actual time=0.087..0.088 rows=0 loops=1)
>> Filter: (NOT is_external_lifecycle_management)
>> Rows Removed by Filter: 334
>> Planning Time: 1.335 ms
>> Execution Time: 6386.792 ms
>>
>> *SET enable_indexscan = OFF;*
>>
>> Limit (cost=84760.55..84760.55 rows=1 width=695) (actual
>> time=0.092..0.093 rows=0 loops=1)
>> -> Sort (cost=84760.55..84822.63 rows=24832 width=695) (actual
>> time=0.092..0.092 rows=0 loops=1)
>> Sort Key: subs.id
>> Sort Method: quicksort Memory: 25kB
>> -> Nested Loop (cost=955.54..84636.39 rows=24832 width=695)
>> (actual time=0.090..0.090 rows=0 loops=1)
>> -> Seq Scan on offer offer1 (cost=0.00..31.31 rows=1
>> width=8) (actual time=0.089..0.089 rows=0 loops=1)
>> Filter: (NOT is_external_lifecycle_management)
>> Rows Removed by Filter: 334
>> -> Bitmap Heap Scan on subscription subs
>> (cost=955.54..83681.53 rows=92355 width=695) (never executed)
>> Recheck Cond: (offer_id = offer1.id)
>> Filter: ((NOT job_in_progress) AND
>> (job_next_process_time < '2022-04-19 09:25:25.535'::timestamp without time
>> zone))
>> -> Bitmap Index Scan on i_fk_subscription_offer
>> (cost=0.00..932.45 rows=93029 width=0) (never executed)
>> Index Cond: (offer_id = offer1.id)
>> Planning Time: 0.266 ms
>> Execution Time: 0.126 ms
>>
> --
>
> Thanks.
>
> Regards,
> Gaurav Anand
>
> [image: logo] <https://bit.ly/3B9rOrW>
>
> This communication is confidential and subject to and governed by Saama’s Electronic
> Communications Disclaimer.
> <https://www.saama.com/email-communication-disclaimer/>
>
>
>