Re: PG Query Planner - Mailing list pgsql-admin

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