pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
4+ messages / 4 participants
[nested] [flat]
* BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
@ 2023-07-28 18:40 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 3 replies; 4+ messages in thread
From: PG Bug reporting form @ 2023-07-28 18:40 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: christian.vallieres@evimbec.ca
The following bug has been logged on the website:
Bug reference: 18042
Logged by: Christian Vallières
Email address: christian.vallieres@evimbec.ca
PostgreSQL version: 15.3
Operating system: Windows 10 x86_64
Description:
Query planner favor starting with the event table and use the 102MB
event_date_idx corresponding to a order by with a limit,
instead of the 94MB event_event_type_id_idx corresponding to the where
condition.
My understanding is that the engine would need to read the event_date_idx
index entirely to find rows matching the where condition.
Using event_event_type_id_idx the engine would only need to find rows
matching the where condition, less rows from a smaller index.
Dropping event_date_idx lead to a plan where it start with unit table then
use event_unit_id_idx on event table which is much better!
I've made the following simplified schema, but can't provide data here since
it weight around 1.3G
I've been able to reproduce the problem with random data, but I need to
remove event_unit_id_idx to get a similar plan.
Ask me if you actually need data.
CREATE TABLE public.unit (
id int4 PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
city_id int4 NOT NULL
);
CREATE INDEX unit_city_id ON public.unit (city_id);
CREATE TABLE public."event" (
id int4 PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
event_type_id int4 NOT NULL,
unit_id int4 NOT NULL,
date timestamp NOT NULL
);
CREATE INDEX event_event_type_id_idx ON public."event" (event_type_id);
CREATE INDEX event_unit_id_idx ON public."event" (unit_id);
CREATE INDEX event_date_idx ON public."event" (date);
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT *
FROM public.unit
INNER JOIN public."event" ON unit.id = "event".unit_id
WHERE unit.city_id=40005
AND event_type_id=1
ORDER BY date
LIMIT 25;
--Bad plan
[{"Plan":{"Node Type":"Limit","Parallel Aware":false,"Startup
Cost":1000.88,"Total Cost":7653.98,"Plan Rows":25,"Plan Width":28,"Actual
Startup Time":3282.866,"Actual Total Time":3283.948,"Actual Rows":0,"Actual
Loops":1,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Shared
Hit Blocks":9382915,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0,"Plans":[{"Node Type":"Gather
Merge","Parent Relationship":"Outer","Parallel Aware":false,"Startup
Cost":1000.88,"Total Cost":497587.81,"Plan Rows":1866,"Plan
Width":28,"Actual Startup Time":3282.864,"Actual Total
Time":3283.947,"Actual Rows":0,"Actual
Loops":1,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Workers
Planned":2,"Workers Launched":2,"Shared Hit Blocks":9382915,"Shared Read
Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit
Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0,"Plans":[{"Node Type":"Nested Loop","Parent
Relationship":"Outer","Parallel Aware":false,"Join Type":"Inner","Startup
Cost":0.86,"Total Cost":496372.4,"Plan Rows":778,"Plan Width":28,"Actual
Startup Time":3273.523,"Actual Total Time":3273.524,"Actual Rows":0,"Actual
Loops":3,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Inner
Unique":true,"Shared Hit Blocks":9382915,"Shared Read Blocks":0,"Shared
Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0,"Workers":[{"Worker Number":0,"Actual Startup Time":3277.105,"Actual
Total Time":3277.105,"Actual Rows":0,"Actual Loops":1,"Shared Hit
Blocks":3106786,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0},{"Worker Number":1,"Actual
Startup Time":3260.872,"Actual Total Time":3260.874,"Actual Rows":0,"Actual
Loops":1,"Shared Hit Blocks":3126968,"Shared Read Blocks":0,"Shared Dirtied
Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0}],"Plans":[{"Node Type":"Index Scan","Parent
Relationship":"Outer","Parallel Aware":true,"Scan
Direction":"Forward","Index Name":"event_date_idx","Relation
Name":"event","Schema":"public","Alias":"event","Startup Cost":0.43,"Total
Cost":273080.27,"Plan Rows":497488,"Plan Width":20,"Actual Startup
Time":2.311,"Actual Total Time":2445.49,"Actual Rows":378663,"Actual
Loops":3,"Output":["event.id","event.event_type_id","event.unit_id","event.date"],"Filter":"(event.event_type_id
= 1)","Rows Removed by Filter":4697889,"Shared Hit Blocks":5554775,"Shared
Read Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0,"Local
Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0,"Workers":[{"Worker Number":0,"Actual Startup
Time":0.041,"Actual Total Time":2444.879,"Actual Rows":374983,"Actual
Loops":1,"Shared Hit Blocks":1841396,"Shared Read Blocks":0,"Shared Dirtied
Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0},{"Worker Number":1,"Actual Startup Time":6.862,"Actual Total
Time":2433.461,"Actual Rows":380614,"Actual Loops":1,"Shared Hit
Blocks":1844508,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0}]},{"Node Type":"Index
Scan","Parent Relationship":"Inner","Parallel Aware":false,"Scan
Direction":"Forward","Index Name":"unit_pkey","Relation
Name":"unit","Schema":"public","Alias":"unit","Startup Cost":0.42,"Total
Cost":0.45,"Plan Rows":1,"Plan Width":8,"Actual Startup Time":0.002,"Actual
Total Time":0.002,"Actual Rows":0,"Actual
Loops":1135989,"Output":["unit.id","unit.city_id"],"Index Cond":"(unit.id =
event.unit_id)","Rows Removed by Index Recheck":0,"Filter":"(unit.city_id =
40005)","Rows Removed by Filter":0,"Shared Hit Blocks":3828140,"Shared Read
Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit
Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0,"Workers":[{"Worker Number":0,"Actual Startup
Time":0.002,"Actual Total Time":0.002,"Actual Rows":0,"Actual
Loops":374983,"Shared Hit Blocks":1265390,"Shared Read Blocks":0,"Shared
Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0},{"Worker Number":1,"Actual Startup Time":0.002,"Actual Total
Time":0.002,"Actual Rows":0,"Actual Loops":380614,"Shared Hit
Blocks":1282460,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0}]}]}]}]},"Planning":{"Shared
Hit Blocks":12,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0},"Planning
Time":0.244,"Triggers":[],"Execution Time":3283.98}]
DROP INDEX event_date_idx;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT *
FROM public.unit
INNER JOIN public."event" ON unit.id = "event".unit_id
WHERE unit.city_id=40005
AND event_type_id=1
ORDER BY date
LIMIT 25;
--Good plan
[{"Plan":{"Node Type":"Limit","Parallel Aware":false,"Startup
Cost":44726.83,"Total Cost":44729.71,"Plan Rows":25,"Plan Width":28,"Actual
Startup Time":24.786,"Actual Total Time":26.094,"Actual Rows":0,"Actual
Loops":1,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Shared
Hit Blocks":4375,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0,"Plans":[{"Node Type":"Gather
Merge","Parent Relationship":"Outer","Parallel Aware":false,"Startup
Cost":44726.83,"Total Cost":44853.1,"Plan Rows":1098,"Plan Width":28,"Actual
Startup Time":24.785,"Actual Total Time":26.092,"Actual Rows":0,"Actual
Loops":1,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Workers
Planned":1,"Workers Launched":1,"Shared Hit Blocks":4375,"Shared Read
Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit
Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0,"Plans":[{"Node Type":"Sort","Parent
Relationship":"Outer","Parallel Aware":false,"Startup Cost":43726.82,"Total
Cost":43729.57,"Plan Rows":1098,"Plan Width":28,"Actual Startup
Time":22.331,"Actual Total Time":22.332,"Actual Rows":0,"Actual
Loops":2,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Sort
Key":["event.date"],"Sort Method":"quicksort","Sort Space Used":25,"Sort
Space Type":"Memory","Shared Hit Blocks":4375,"Shared Read Blocks":0,"Shared
Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0,"Workers":[{"Worker Number":0,"Actual Startup Time":20.152,"Actual
Total Time":20.153,"Actual Rows":0,"Actual Loops":1,"Sort
Method":"quicksort","Sort Space Used":25,"Sort Space Type":"Memory","Shared
Hit Blocks":2141,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0}],"Plans":[{"Node
Type":"Nested Loop","Parent Relationship":"Outer","Parallel
Aware":false,"Join Type":"Inner","Startup Cost":0.43,"Total
Cost":43695.84,"Plan Rows":1098,"Plan Width":28,"Actual Startup
Time":22.313,"Actual Total Time":22.314,"Actual Rows":0,"Actual
Loops":2,"Output":["unit.id","unit.city_id","event.id","event.event_type_id","event.unit_id","event.date"],"Inner
Unique":false,"Shared Hit Blocks":4366,"Shared Read Blocks":0,"Shared
Dirtied Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0,"Workers":[{"Worker Number":0,"Actual Startup Time":20.121,"Actual
Total Time":20.121,"Actual Rows":0,"Actual Loops":1,"Shared Hit
Blocks":2132,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared
Written Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write Time":0}],"Plans":[{"Node Type":"Seq
Scan","Parent Relationship":"Outer","Parallel Aware":true,"Relation
Name":"unit","Schema":"public","Alias":"unit","Startup Cost":0,"Total
Cost":16581.3,"Plan Rows":588,"Plan Width":8,"Actual Startup
Time":7.566,"Actual Total Time":21.818,"Actual Rows":256,"Actual
Loops":2,"Output":["unit.id","unit.city_id"],"Filter":"(unit.city_id =
40005)","Rows Removed by Filter":319366,"Shared Hit Blocks":2829,"Shared
Read Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0,"Local
Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0,"Workers":[{"Worker Number":0,"Actual Startup
Time":5.368,"Actual Total Time":19.58,"Actual Rows":286,"Actual
Loops":1,"Shared Hit Blocks":1273,"Shared Read Blocks":0,"Shared Dirtied
Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0}]},{"Node Type":"Index Scan","Parent Relationship":"Inner","Parallel
Aware":false,"Scan Direction":"Forward","Index
Name":"event_unit_id_idx","Relation
Name":"event","Schema":"public","Alias":"event","Startup Cost":0.43,"Total
Cost":46.06,"Plan Rows":5,"Plan Width":20,"Actual Startup
Time":0.002,"Actual Total Time":0.002,"Actual Rows":0,"Actual
Loops":512,"Output":["event.id","event.event_type_id","event.unit_id","event.date"],"Index
Cond":"(event.unit_id = unit.id)","Rows Removed by Index
Recheck":0,"Filter":"(event.event_type_id = 1)","Rows Removed by
Filter":0,"Shared Hit Blocks":1537,"Shared Read Blocks":0,"Shared Dirtied
Blocks":0,"Shared Written Blocks":0,"Local Hit Blocks":0,"Local Read
Blocks":0,"Local Dirtied Blocks":0,"Local Written Blocks":0,"Temp Read
Blocks":0,"Temp Written Blocks":0,"I/O Read Time":0,"I/O Write
Time":0,"Workers":[{"Worker Number":0,"Actual Startup Time":0.002,"Actual
Total Time":0.002,"Actual Rows":0,"Actual Loops":286,"Shared Hit
Blocks":859,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared Written
Blocks":0,"Local Hit Blocks":0,"Local Read Blocks":0,"Local Dirtied
Blocks":0,"Local Written Blocks":0,"Temp Read Blocks":0,"Temp Written
Blocks":0,"I/O Read Time":0,"I/O Write
Time":0}]}]}]}]}]},"Planning":{"Shared Hit Blocks":18,"Shared Read
Blocks":0,"Shared Dirtied Blocks":1,"Shared Written Blocks":0,"Local Hit
Blocks":0,"Local Read Blocks":0,"Local Dirtied Blocks":0,"Local Written
Blocks":0,"Temp Read Blocks":0,"Temp Written Blocks":0,"I/O Read
Time":0,"I/O Write Time":0},"Planning Time":0.336,"Triggers":[],"Execution
Time":26.126}]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
@ 2023-07-28 19:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-07-28 19:15 UTC (permalink / raw)
To: christian.vallieres@evimbec.ca; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Query planner favor starting with the event table and use the 102MB
> event_date_idx corresponding to a order by with a limit,
> instead of the 94MB event_event_type_id_idx corresponding to the where
> condition.
Sadly, this isn't something we can do much about. Estimating the
behavior of a query with ORDER BY and a small LIMIT is simply very
tricky: a plan that depends on an indexscan stopping early might
be very fast, or it might not be, depending on factors such as row
physical locations that the planner doesn't have a lot of info about.
Your best bet might be to prevent event_date_idx from matching the
query's sort order. Seeing that date is NOT NULL, I'd suggest
changing either the index or the query (not both!) to specify NULLS
FIRST. This'll make no actual difference given the lack of nulls, but
the planner isn't cognizant of that and will decide it can't use the
index in this way for this query.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
@ 2023-07-28 21:02 Jeff Janes <jeff.janes@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2023-07-28 21:02 UTC (permalink / raw)
To: christian.vallieres@evimbec.ca; pgsql-bugs@lists.postgresql.org
You report this as a bug against 15.3, did it used to perform better in a
previous version or is this the only version you tried it in?
Your plans are unreadable. We are people here, not machines; you should
use FORMAT text, not FORMAT json, and post them in a way that preserves
newline and indentations. Email is bad at that, so maybe share as links to
https://explain.depesz.com/ in addition to or instead of embedded text.
And this doesn't seem to be a bug, just an infamous performance problem, so
maybe use the pgsql-performance list instead.
You could likely get a big improvement by having an index on
(event_type_id, date). It would still be using the wrong (suboptimal)
index, but it would be a lot less wrong, as it could rule out the
disqualified event_type_id without needing to inspect each one. It is also
likely to be "good enough" over a wide range of parameters, unlike Tom's
suggestion which would probably be very good for this particular
parameterization, but might not be good for a different one with a much
more popular city_id.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where
@ 2023-07-29 14:45 Christian Vallières <christian.vallieres@evimbec.ca>
parent: PG Bug reporting form <noreply@postgresql.org>
2 siblings, 0 replies; 4+ messages in thread
From: Christian Vallières @ 2023-07-29 14:45 UTC (permalink / raw)
To: Christian Vallières <christian.vallieres@evimbec.ca>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Sorry I've replied only to Jeff by mistake, Jeff you could discard that precedent mail, I've added things here.
I have tested it with 13.11, 14.8 and they all do the same.
Sorry about the newline the email added in the one-liner json, otherwise it could have been pasted in explain.depesz.com even in json.
I know I can make an (event_type_id, date) index, this is the first thing I did, but it's still far from optimal. Even a covering index including all needed columns is far from optimal.
https://explain.depesz.com/s/8NEk slow using event_date_idx. 3 seconds
https://explain.depesz.com/s/lE8R with an index on (event_type_id, date). 1 seconds.
https://explain.depesz.com/s/99nK with a covering index. 500ms.
https://explain.depesz.com/s/l9da fast even if it seq scan on unit table (real table is larger and an index is used). 26 milliseconds.
Without changing the indexes, I already figured I could use a function like coalesce in the order by that won't change anything since the columns aren't nullable and it would prevent the planner from considering the index.
The nulls first seem a bit better solution, not involving a function. But it's a bit hacky, a newbie could decide to remove that and reintroduce the performance problem.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-07-29 14:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-28 18:40 BUG #18042: Query planner favor index corresponding to a order by with a limit even when there is a where PG Bug reporting form <noreply@postgresql.org>
2023-07-28 19:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-28 21:02 ` Jeff Janes <jeff.janes@gmail.com>
2023-07-29 14:45 ` Christian Vallières <christian.vallieres@evimbec.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox