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

Groups > comp.databases.postgresql > #234

Re: Case-insensitive select

Show all headers | View raw

On Mon, 05 Sep 2011 16:13:44 +0200, Laurenz Albe wrote:


Hmmm yes, you're right, I ran your test on 9.0.2, the version available 
for Fedora 14 and it does have histogram on index, in additon to the 
histogram on the column itself. I must admit I didn't think of using the 
index name as a table name in pg_stats. Oracle histograms are related to 
table columns, not to indexes, which seems logical to me.


mgogala=# CREATE TABLE test(id serial primary key, val varchar(20) NOT 
NULL);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for 
serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" 
for table "test"
CREATE TABLE
mgogala=# DO $$BEGIN
mgogala$#    FOR i IN 1..10000 LOOP
mgogala$#       INSERT INTO test(val) VALUES (substr(random()::text, 3));
mgogala$#    END LOOP;
mgogala$# END;$$;
DO
mgogala=# 
mgogala=# CREATE INDEX test_val_ind ON test(lower(val));
CREATE INDEX
mgogala=# ANALYZE test;
ANALYZE
mgogala=# SELECT array_length(histogram_bounds, 1) FROM pg_stats
mgogala-#    WHERE tablename = 'test_val_ind';
 array_length 
--------------
          101
(1 row)

mgogala=# SELECT attname FROM pg_stats WHERE tablename = 'test_val_ind';
 attname 
---------
 lower
(1 row)

mgogala=# SELECT array_length(histogram_bounds, 1) FROM pg_stats
mgogala-# where tablename='test' and attname='val';
 array_length 
--------------
          101
(1 row)



mgogala=# select version();
                                                  
version                       
                           
--------------------------------------------------------------------------------
---------------------------
 PostgreSQL 9.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.5.1 
20100924
 (Red Hat 4.5.1-4), 32-bit
(1 row)


-- 
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