public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kenny Bachman <[email protected]>
To: [email protected]
Subject: PG Query Planner
Date: Tue, 19 Apr 2022 20:36:40 +0300
Message-ID: <CAC0w7L+wLa6ATLFrmMKui+q0XZhgxNLptpge_1BF+L0ULFeedg@mail.gmail.com> (raw)
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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PG Query Planner
In-Reply-To: <CAC0w7L+wLa6ATLFrmMKui+q0XZhgxNLptpge_1BF+L0ULFeedg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox