On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: > I was thinking that a index on lower(substr(l_name, 1, 1)) and another > index on lower(substr(org_name, 1, 1)) should gives better performances. > When I've to create theses two indexes, it seems like this is not > allowed : > > levure=> CREATE INDEX firstchar_lastname_idx ON > people(lower(substr(l_name, 1, 1))); > ERROR: parser: parse error at or near "(" at character 59
In 7.4, I believe you would say on people((lower(substr(l_name,1,1)))) but I'm not sure that index would really help in practice. > Do you have another idea to get better performances ? In addition to what Tom said, the row estimates look suspiciously default. You mention vacuuming, but do you ever analyze the tables? Also, what do you have sort_mem set to? ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings