pg.ddx.io pgsql-hackers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
distinguish index cost component from table component 3+ messages / 2 participants [nested] [flat]
Related threads (commitfest entry [doc] alter table references bogus table-specific planner parameters/2417): Re: doc: alter table references bogus table-specific planner parameters
* distinguish index cost component from table component @ 2020-01-03 14:14 Justin Pryzby <pryzby@telsasoft.com> 0 siblings, 1 reply; 3+ messages in thread From: Justin Pryzby @ 2020-01-03 14:14 UTC (permalink / raw) To: pgsql-performance@postgresql.org Is it possible to tell what component of the cost estimate of an index scan is from the index reads vs heap ? It would help to be able to set enable_bitmapscan=FORCE (to make all index scans go through a bitmap). Adding OR conditions can sometimes do this. That includes cost of bitmap manipulation, but it's good enough for me. Or maybe explain should report it. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: distinguish index cost component from table component @ 2020-01-03 14:33 Jeff Janes <jeff.janes@gmail.com> parent: Justin Pryzby <pryzby@telsasoft.com> 0 siblings, 1 reply; 3+ messages in thread From: Jeff Janes @ 2020-01-03 14:33 UTC (permalink / raw) To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performa. <pgsql-performance@postgresql.org> On Fri, Jan 3, 2020 at 9:14 AM Justin Pryzby <pryzby@telsasoft.com> wrote: > Is it possible to tell what component of the cost estimate of an index > scan is > from the index reads vs heap ? > Not that I have found, other than through sprinkling elog statements throughout the costing code. Which is horrible, because then you get estimates for all the considered but rejected index scans as well, but without the context to know what they are for. So it only works for toy queries where there are few possible indexes to consider. It would help to be able to set enable_bitmapscan=FORCE (to make all index > scans go through a bitmap). Doesn't enable_indexscan=off accomplish this already? It is possible but not terribly likely to switch from index to seq, rather than from index to bitmap. (Unless the index scan was being used to obtain an ordered result, but a hypothetical enable_bitmapscan=FORCE can't fix that). Of course this doesn't really answer your question, as the separately-reported costs of a bitmap heap and bitmap index scan are unlikely to match what the costs would be of a regular index scan, if they were reported separately. Or maybe explain should report it. > I wouldn't be optimistic about getting such a backwards-incompatible change accepted (plus it would surely add some small accounting overhead, which again would probably not be acceptable). But if you do enough tuning work, perhaps it would be worth carrying an out-of-tree patch to implement that. I wouldn't be so interested in writing such a patch, but would be interested in using one were it available somewhere. Cheers, Jeff ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: distinguish index cost component from table component @ 2020-01-03 16:03 Justin Pryzby <pryzby@telsasoft.com> parent: Jeff Janes <jeff.janes@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2020-01-03 16:03 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance@postgresql.org On Fri, Jan 03, 2020 at 09:33:35AM -0500, Jeff Janes wrote: > Of course this doesn't really answer your question, as the > separately-reported costs of a bitmap heap and bitmap index scan are > unlikely to match what the costs would be of a regular index scan, if they > were reported separately. I think the cost of index component of bitmap scan would be exactly the same as the cost of the original indexscan. >> Or maybe explain should report it. > > I wouldn't be optimistic about getting such a backwards-incompatible change > accepted (plus it would surely add some small accounting overhead, which > again would probably not be acceptable). But if you do enough tuning work, > perhaps it would be worth carrying an out-of-tree patch to implement that. > I wouldn't be so interested in writing such a patch, but would be > interested in using one were it available somewhere. I did the attached in the simplest possible way. If it's somehow possible get the path's index_total_cost from the plan, then there'd be no additional overhead. Justin Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadv1-0001-explain-show-index_total_cost.patch (text/x-diff, 1.9K) download
From 52ac676bdef1aa0a1fa18520a69d267fbb93eb19 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryzbyj@telsasoft.com> Date: Fri, 3 Jan 2020 09:53:31 -0600 Subject: [PATCH v1] explain: show index_total_cost --- src/backend/commands/explain.c | 3 +++ src/backend/optimizer/plan/createplan.c | 1 + src/include/nodes/plannodes.h | 1 + 3 files changed, 5 insertions(+) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 0910d91..8973c86 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1614,6 +1614,9 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + if (es->costs && es->verbose) // && verbose ? + ExplainPropertyFloat("Index Total Cost", NULL, + planstate->plan->indextotalcost, 2, es); break; case T_IndexOnlyScan: show_scan_qual(((IndexOnlyScan *) plan)->indexqual, diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index dff826a..c0419bc 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -2924,6 +2924,7 @@ create_indexscan_plan(PlannerInfo *root, best_path->indexscandir); copy_generic_path_info(&scan_plan->plan, &best_path->path); + ((Plan *)scan_plan)->indextotalcost = best_path->indextotalcost; return scan_plan; } diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 32c0d87..711c9d8 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -122,6 +122,7 @@ typedef struct Plan */ Cost startup_cost; /* cost expended before fetching any tuples */ Cost total_cost; /* total cost (assuming all tuples fetched) */ + Cost indextotalcost; /* total cost of index */ /* * planner's estimate of result size of this plan step -- 2.7.4
end of thread, other threads:[~2020-01-03 16:03 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-01-03 14:14 distinguish index cost component from table component Justin Pryzby <pryzby@telsasoft.com> 2020-01-03 14:33 ` Jeff Janes <jeff.janes@gmail.com> 2020-01-03 16:03 ` Justin Pryzby <pryzby@telsasoft.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