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

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

Mladen Gogala wrote:
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

On Mon, 05 Sep 2011 09:45:13 +0200, Laurenz Albe wrote:

I don't understand? What does this prove? That there is a histogram on
lower(val)? How does your example prove that?



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
I thought that was clear, sorry.

There is a histogram in the index statistics, and the index is on lower(val).
So yes, there is a histogram on lower(val), used whenever the planner
considers the index.

The index has only one "column", namly lower(val).

SELECT attname FROM pg_stats WHERE tablename = 'test_val_ind';

attname
---------
lower
(1 row)

pg_statistic (and its derived view pg_stats) has entries for all
indexable columns and index espressions.

Yours,
Laurenz Albe

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