Re: PG Query Planner

[Kenny Bachman <kenny.bachman17@xxxxxxxxx>]

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.anand@xxxxxxxxx>, 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.bachman17@xxxxxxxxx> 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

This communication is confidential and subject to and governed by Saama’s Electronic Communications Disclaimer.