Re: Index of a table is not used (in any case) - Mailing list pgsql-hackers

From Zeugswetter Andreas SB SD
Subject Re: Index of a table is not used (in any case)
Date
Msg-id 46C15C39FEB2C44BA555E356FBCD6FA41EB3D4@m0114.s-mxs.net
Whole thread Raw
In response to Index of a table is not used (in any case)  (Reiner Dassing <dassing@wettzell.ifag.de>)
List pgsql-hackers
> > > *very* slow, due to seq scan on
> > > 20 million entries, which is a test setup up to now)
> >
> > Perennial first question: did you VACUUM ANALYZE?
> 
> Can there, or could there, be a notion of "rule based" optimization of
> queries in PostgreSQL? The "not using index" problem is probably the
most
> common and most misunderstood problem.

There is a (sort of) rule based behavior in PostgreSQL, 
the down side of the current implementation is, that certain 
other commands than ANALYZE (e.g. "create index") partly update 
optimizer statistics. This is bad behavior, since then only part 
of the statistics are accurate. Statistics always have to be seen 
in context to other table's and other index'es statistics. 

Thus, currently the rule based optimizer only works if you create 
the indexes on empty tables (before loading data), which obviously 
has downsides. Else you have no choice but to ANALYZE frequently.

I have tried hard to fight for this pseudo rule based behavior, 
but was only partly successful in convincing core. My opinion is, 
that (unless runtime statistics are kept) no other command than 
ANALYZE should be allowed to touch optimizer relevant statistics 
(maybe unless explicitly told to).

Andreas


pgsql-hackers by date:

Previous
From: "Mikheev, Vadim"
Date:
Subject: Re: [GENERAL] Database corruption?
Next
From: "mario"
Date:
Subject: copying a large object?