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

Groups > comp.databases.postgresql > #231

Re: Case-insensitive select

Show all headers | View raw

Mladen Gogala 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.

Why do you claim that without a test?
This is PostgreSQL 9.1:

CREATE TABLE test(id serial primary key, val varchar(20) NOT NULL);

DO $$BEGIN
   FOR i IN 1..10000 LOOP
      INSERT INTO test(val) VALUES (substr(random()::text, 3));
   END LOOP;
END;$$;

CREATE INDEX test_val_ind ON test(lower(val));

SHOW default_statistics_target;

 default_statistics_target
---------------------------
 100
(1 row)

ANALYZE test;

SELECT array_length(histogram_bounds, 1) FROM pg_stats
   WHERE tablename = 'test_val_ind';

 array_length
--------------
          101
(1 row)

Yours,
Laurenz Albe 

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