Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #230

Re: Case-insensitive select

Show all headers | View raw

On Fri, 02 Sep 2011 10:08:19 +0200, Laurenz Albe wrote:

> At any rate statistics for such indexes are collected (look for rows in
> pg_stats that belong to the index), and these statistice are exactly the
> same as would be collected for the redundant column, so why should there
> be a difference?

The problem is in the histograms, which are collected per column, not per 
index. You don't have histograms on lower(col1)  unless it's in a 
separate column. Without histograms, the optimizer can only make an 
assumption, usually the uniform distribution of values, and use the 
number of keys in the index divided by the number of rows in the table 
for the selectivity estimate. That is not necessarily accurate and can 
lead to bad performance. If the values in the "citext" column are skewed, 
there are many people named "Smith" in the population, you will get an 
index search when that is inappropriate.



-- 
http://mgogala.byethost5.com

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Case-insensitive select RVince <rvince99@gmail.com> - 2011-08-31 05:29 -0700
  Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 13:19 +0000
  Re: Case-insensitive select Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-08-31 20:42 +0100
    Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 23:00 +0000
      Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 08:56 +0200
        Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 12:57 +0000
          Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 16:40 +0200
            Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 21:26 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-02 13:03 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 09:45 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-05 10:53 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 16:13 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-06 02:21 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200

csiph-web