> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 16 January 2004 21:50
> To: Martin Hampl
> Cc: pgsql-general@postgresql.org; Mark Cave-Ayland
> Subject: Re: [GENERAL] indexing with lower(...) -> queries
> are not optimised very well - Please Help
>
>
> Martin Hampl <Martin.Hampl@gmx.de> writes:
> > Am 21.11.2003 um 06:54 schrieb Tom Lane:
> >> [ bad plan for use of a functional index ]
> >>
> >> The rows estimate (and therefore also the cost estimate) is a
> >> complete guess in this situation, because the system keeps no
> >> statistics about the values of lower(word). Improving
> this situation
> >> is on the TODO list.
>
> > Any ideas when this will work? Is it difficult to implement?
>
> It strikes me as a small-but-not-trivial project. Possibly
> someone will get it done for 7.5. You can find some
> discussion in the pghackers archives, IIRC (look for threads
> about keeping statistics on functional indexes).
>
> This brings up a thought for Mark Cave-Ayland's project of
> breaking out the datatype dependencies in ANALYZE: it would
> be wise to ensure that the API for examine_attribute doesn't
> depend too much on the assumption that the value(s) being
> analyzed are part of the relation proper. They might be
> coming from a functional index, or even more likely being
> computed on-the-fly based on the definition of a functional
> index. Not sure what we'd want to change exactly, but it's
> something to think about before the API gets set in stone.
>
> regards, tom lane
Hi Tom/Martin,
I'm currently about 2/3rds of the way through writing my patch so things
are very nearly where I want them to be. In its current form it is just
the reorganisation and extension of what is already there, so I haven't
looked at the implications of functional indexes. It would be great if
the completed and checked patch can be applied so at least it gets out
there - then other developers can look at how this works for things like
functional indexes and put in the API changes required before the 7.5
feature freeze.
Cheers,
Mark.
---
Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England
Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.
