pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIs it possible to specify minimum number of rows planner should consider?
4+ messages / 3 participants
[nested] [flat]
* Is it possible to specify minimum number of rows planner should consider?
@ 2020-09-28 20:12 Timothy Garnett <tgarnett@panjiva.com>
2020-09-28 21:06 ` Re: Is it possible to specify minimum number of rows planner should consider? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Timothy Garnett @ 2020-09-28 20:12 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Is there some way to tell the planner that unless it's guaranteed by a
constraint or some such it shouldn't guess that the selectivity of a
filter/anti-join is 1 row (e.g. minimum to consider is 2 rows unless it's
guaranteed to be 1 row) or somehow otherwise make it more conservative
around the worst case possibilities. I feel like this would cover something
like 1/3 of the more problematic planner performance issues I run into. The
kind where a query suddenly runs 60,000 times slower than it did
previously. I can live with some queries being slightly slower if I can
avoid the case where they will all of sudden never complete.
My current motivating example is this (... abridged) query:
postgresql 11.7 on ubuntu linux
-> **Nested Loop Left Join** (cost=3484616.45..5873755.65 rows=1
width=295)
Join Filter: (hsc.c_field = c.id)
...
-> *Nested Loop Left Join (cost=1072849.19..3286800.99 rows=1
width=190)*
-> Hash Anti Join (cost=1072848.62..3286798.53
***rows=1***[actually 65k] width=189)
Hash Cond: (c.id = trc.field)
-> Seq Scan on c (cost=0.00..1096064.73
rows=14328573 width=189)
-> Hash (cost=830118.31..830118.31 rows=14794985
width=4)
-> Seq Scan on trc (cost=0.00..830118.31
rows=14794985 width=4)
-> Index Scan using con_pkey on con (cost=0.56..2.46
rows=1 width=9)
Index Cond: (c.con_field = id)
...
-> Unique (cost=2411766.83..2479065.82 rows=4794957 width=29)
-> Sort (cost=2411766.83..2445416.33 rows=13459797 width=29)
Sort Key: hsc.c_field, xxx
-> Hash Join (cost=11143.57..599455.83 rows=13459797
width=29)
...
*** is where the planner is off in it's row estimation
c.id is unique for that table, statistics set to 10k and freshly analyzed
trc.field is unique for that table, statistics set to 10k and freshly
analyzed
row estimates for those tables are pretty close to correct (within a couple
of %)
there is no foreign key constraint between those two tables
c.id and trc.field are both integers with pretty similar distributions over
1...22 million
** is where it picks a disastrous join plan based on that misstaken-row
estimate
this has to be a close call with doing a merge_join as the other side is
already sorted
* this join is ok, since even if it isn't the fastest join here with the
correct row count, given the index it's not much worse
I can work around this by breaking up the query (e.g. creating a temporary
table of the selected ids, analyzing it then using it in the rest of the
query) or by temporarily disabling nestedloop joins (which makes other
parts of the query slower, but not dramatically so), but is there some
other reasonable proactive way to avoid it? It was running fine for a year
before blowing up (trigger is I suspect the trc table getting enough larger
than the c table, originally it was smaller) and I hit similarish kinds of
issues every so often.
Tim
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible to specify minimum number of rows planner should consider?
2020-09-28 20:12 Is it possible to specify minimum number of rows planner should consider? Timothy Garnett <tgarnett@panjiva.com>
@ 2020-09-28 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-28 21:45 ` Re: Is it possible to specify minimum number of rows planner should consider? Matthew Bellew <matthewb@labkey.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-09-28 21:06 UTC (permalink / raw)
To: Timothy Garnett <tgarnett@panjiva.com>; +Cc: pgsql-performance@lists.postgresql.org
Timothy Garnett <tgarnett@panjiva.com> writes:
> Is there some way to tell the planner that unless it's guaranteed by a
> constraint or some such it shouldn't guess that the selectivity of a
> filter/anti-join is 1 row (e.g. minimum to consider is 2 rows unless it's
> guaranteed to be 1 row) or somehow otherwise make it more conservative
> around the worst case possibilities.
There's been some discussion in that area, but it's a hard problem
to solve in general, and especially so if you'd like to not break
a ton of queries that work nicely today.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible to specify minimum number of rows planner should consider?
2020-09-28 20:12 Is it possible to specify minimum number of rows planner should consider? Timothy Garnett <tgarnett@panjiva.com>
2020-09-28 21:06 ` Re: Is it possible to specify minimum number of rows planner should consider? Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-28 21:45 ` Matthew Bellew <matthewb@labkey.com>
2020-09-29 00:22 ` Re: Is it possible to specify minimum number of rows planner should consider? Timothy Garnett <tgarnett@panjiva.com>
0 siblings, 1 reply; 4+ messages in thread
From: Matthew Bellew @ 2020-09-28 21:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Timothy Garnett <tgarnett@panjiva.com>; pgsql-performance@lists.postgresql.org
Here is a commit that accomplishes this with a configuration parameter.
https://github.com/labkey-matthewb/postgres/commit/b1fd99f4deffbbf3db2172ccaba51a34f18d1b1a
On Mon, Sep 28, 2020 at 2:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Timothy Garnett <tgarnett@panjiva.com> writes:
> > Is there some way to tell the planner that unless it's guaranteed by a
> > constraint or some such it shouldn't guess that the selectivity of a
> > filter/anti-join is 1 row (e.g. minimum to consider is 2 rows unless it's
> > guaranteed to be 1 row) or somehow otherwise make it more conservative
> > around the worst case possibilities.
>
> There's been some discussion in that area, but it's a hard problem
> to solve in general, and especially so if you'd like to not break
> a ton of queries that work nicely today.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Is it possible to specify minimum number of rows planner should consider?
2020-09-28 20:12 Is it possible to specify minimum number of rows planner should consider? Timothy Garnett <tgarnett@panjiva.com>
2020-09-28 21:06 ` Re: Is it possible to specify minimum number of rows planner should consider? Tom Lane <tgl@sss.pgh.pa.us>
2020-09-28 21:45 ` Re: Is it possible to specify minimum number of rows planner should consider? Matthew Bellew <matthewb@labkey.com>
@ 2020-09-29 00:22 ` Timothy Garnett <tgarnett@panjiva.com>
0 siblings, 0 replies; 4+ messages in thread
From: Timothy Garnett @ 2020-09-29 00:22 UTC (permalink / raw)
To: Matthew Bellew <matthewb@labkey.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
That's a really straightforward patch that looks pretty safe, I may play
around with that a bit.
Thanks,
Tim
On Mon, Sep 28, 2020 at 5:45 PM Matthew Bellew <matthewb@labkey.com> wrote:
> Here is a commit that accomplishes this with a configuration parameter.
>
>
> https://github.com/labkey-matthewb/postgres/commit/b1fd99f4deffbbf3db2172ccaba51a34f18d1b1a
>
> On Mon, Sep 28, 2020 at 2:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Timothy Garnett <tgarnett@panjiva.com> writes:
>> > Is there some way to tell the planner that unless it's guaranteed by a
>> > constraint or some such it shouldn't guess that the selectivity of a
>> > filter/anti-join is 1 row (e.g. minimum to consider is 2 rows unless
>> it's
>> > guaranteed to be 1 row) or somehow otherwise make it more conservative
>> > around the worst case possibilities.
>>
>> There's been some discussion in that area, but it's a hard problem
>> to solve in general, and especially so if you'd like to not break
>> a ton of queries that work nicely today.
>>
>> regards, tom lane
>>
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-09-29 00:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-28 20:12 Is it possible to specify minimum number of rows planner should consider? Timothy Garnett <tgarnett@panjiva.com>
2020-09-28 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-28 21:45 ` Matthew Bellew <matthewb@labkey.com>
2020-09-29 00:22 ` Timothy Garnett <tgarnett@panjiva.com>
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