pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: index cost estimation
3+ messages / 2 participants
[nested] [flat]
* Re: index cost estimation
@ 2022-07-06 14:41 Tom Lane <tgl@sss.pgh.pa.us>
2022-07-06 14:52 ` Re: index cost estimation Ronan Dunklau <ronan.dunklau@aiven.io>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2022-07-06 14:41 UTC (permalink / raw)
To: Ronan Dunklau <ronan.dunklau@aiven.io>; +Cc: Hung Nguyen <hungnq1989@gmail.com>; pgsql-bugs@lists.postgresql.org, "rjuju123@gmail.com" <rjuju123@gmail.com>
Ronan Dunklau <ronan.dunklau@aiven.io> writes:
> Looking into it, it looks like we are not charging a cpu "descent" cost for
> the entry tree of the gin index, which we do for the btree index. In general,
> it does not pose a problem since IO costs are far greater than cpu costs. But
> when the index scan is inside a nestloop, we account for cache effect and
> amortize the cost of IO over the number of outer scans, which reduces its
> relative importance significantly. In that case, the index scan on the gin
> index appears much cheaper, as the constant cpu cost is not taken into
> account.
Hm, so it'd seem this probably could happen when comparing *any*
non-btree index to a btree index, because I don't think we are
particularly careful with CPU cost estimation for any of the
other index types. If we do something about this, we probably
have to look at all of them.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index cost estimation
2022-07-06 14:41 Re: index cost estimation Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-07-06 14:52 ` Ronan Dunklau <ronan.dunklau@aiven.io>
2022-07-06 15:42 ` Re: index cost estimation Ronan Dunklau <ronan.dunklau@aiven.io>
0 siblings, 1 reply; 3+ messages in thread
From: Ronan Dunklau @ 2022-07-06 14:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hung Nguyen <hungnq1989@gmail.com>; pgsql-bugs@lists.postgresql.org, "rjuju123@gmail.com" <rjuju123@gmail.com>
Le mercredi 6 juillet 2022, 16:41:29 CEST Tom Lane a écrit :
> Hm, so it'd seem this probably could happen when comparing *any*
> non-btree index to a btree index, because I don't think we are
> particularly careful with CPU cost estimation for any of the
> other index types. If we do something about this, we probably
> have to look at all of them.
For gist and sp-gist, a descent cost is taken into account, by estimating the
tree height so that particular effect is mitigated. Whether the cpu cost
estimation is sensible regarding to btree is another topic, but at least the
index cost doesn't vanish when inside a loop.
Hash, brin and bloom are quite different, so maybe another examination would be
required but probably outside the scope of this bug report.
--
Ronan Dunklau
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index cost estimation
2022-07-06 14:41 Re: index cost estimation Tom Lane <tgl@sss.pgh.pa.us>
2022-07-06 14:52 ` Re: index cost estimation Ronan Dunklau <ronan.dunklau@aiven.io>
@ 2022-07-06 15:42 ` Ronan Dunklau <ronan.dunklau@aiven.io>
0 siblings, 0 replies; 3+ messages in thread
From: Ronan Dunklau @ 2022-07-06 15:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org; +Cc: Hung Nguyen <hungnq1989@gmail.com>; pgsql-bugs@lists.postgresql.org, "rjuju123@gmail.com" <rjuju123@gmail.com>; Ronan Dunklau <ronan.dunklau@aiven.io>
Le mercredi 6 juillet 2022, 16:52:09 CEST Ronan Dunklau a écrit :
> Le mercredi 6 juillet 2022, 16:41:29 CEST Tom Lane a écrit :
> > Hm, so it'd seem this probably could happen when comparing *any*
> > non-btree index to a btree index, because I don't think we are
> > particularly careful with CPU cost estimation for any of the
> > other index types. If we do something about this, we probably
> > have to look at all of them.
>
> For gist and sp-gist, a descent cost is taken into account, by estimating
the
> tree height so that particular effect is mitigated. Whether the cpu cost
> estimation is sensible regarding to btree is another topic, but at least the
> index cost doesn't vanish when inside a loop.
>
> Hash, brin and bloom are quite different, so maybe another examination would
be
> required but probably outside the scope of this bug report.
Here is a patch tentatively addressing the problem. I'm not sure what I'm
doing with the number of searched entries is right though.
--
Ronan Dunklau
Attachments:
v1-0001-Fix-gin-costing.patch (text/x-patch, 0B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-07-06 15:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-06 14:41 Re: index cost estimation Tom Lane <tgl@sss.pgh.pa.us>
2022-07-06 14:52 ` Ronan Dunklau <ronan.dunklau@aiven.io>
2022-07-06 15:42 ` Ronan Dunklau <ronan.dunklau@aiven.io>
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