Re: PG Query Planner

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/>