Thread: Re: indexing with lower(...) -> queries are not optimised very well - Please Help
Re: indexing with lower(...) -> queries are not optimised very well - Please Help
From
"Mark Cave-Ayland"
Date:
> -----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.