On Sat, Oct 18, 2014 at 02:20:45PM -0400, Bruce Momjian wrote:
> On Sat, Oct 18, 2014 at 06:15:03PM +0200, Marko Tiikkaja wrote:
> > On 10/18/14, 5:46 PM, Tom Lane wrote:
> > >Marko Tiikkaja <[email protected]> writes:
> > >>Yes, exactly; if I had had the option to disable the index from the
> > >>optimizer's point of view, I'd have seen that it's not used for looking
> > >>up any data by any queries, and thus I would have known that I can
> > >>safely drop it without slowing down queries. Which was the only thing I
> > >>cared about, and where the stats we provide failed me.
> > >
> > >This argument is *utterly* wrongheaded, because it assumes that the
> > >planner's use of the index provided no benefit to your queries. If the
> > >planner was touching the index at all then it was planning queries in
> > >which knowledge of the extremal value was relevant to accurate selectivity
> > >estimation. So it's quite likely that without the index you'd have gotten
> > >different and inferior plans, whether or not those plans actually chose to
> > >use the index.
> >
> > Maybe. But at the same time that's a big problem: there's no way of
> > knowing whether the index is actually useful or not when it's used
> > only by the query planner.
>
> That is a good point. Without an index, the executor is going to do a
> sequential scan, while a missing index to the optimizer just means worse
> statistics.
I have applied the attached patch to document that the optimizer can
increase the index usage statistics.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
new file mode 100644
index afcfb89..71d06ce
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*************** postgres 27093 0.0 0.0 30096 2752
*** 1382,1389 ****
</para>
<para>
! Indexes can be used via either simple index scans or <quote>bitmap</>
! index scans. In a bitmap scan
the output of several indexes can be combined via AND or OR rules,
so it is difficult to associate individual heap row fetches
with specific indexes when a bitmap scan is used. Therefore, a bitmap
--- 1382,1389 ----
</para>
<para>
! Indexes can be used by simple index scans, <quote>bitmap</> index scans,
! and the optimizer. In a bitmap scan
the output of several indexes can be combined via AND or OR rules,
so it is difficult to associate individual heap row fetches
with specific indexes when a bitmap scan is used. Therefore, a bitmap
*************** postgres 27093 0.0 0.0 30096 2752
*** 1393,1398 ****
--- 1393,1401 ----
<structname>pg_stat_all_tables</>.<structfield>idx_tup_fetch</>
count for the table, but it does not affect
<structname>pg_stat_all_indexes</>.<structfield>idx_tup_fetch</>.
+ The optimizer also accesses indexes to check for supplied constants
+ whose values are outside the recorded range of the optimizer statistics
+ because the optimizer statistics might be stale.
</para>
<note>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
