Hi all,
I see a strange behavior ( for me ) on 9.2 (but seems the same on
9.1 and 9.3) of the optimizer on query like that :
/* create a table with random data and 20000 rows */
create table test1 ( id int not null primary key, state1 int not null
default 0, state2 int not null default 0, state3 int not null default 0 );
insert into test1 (id, state1, state2, state3) select i,
(random()*3)::int, (random())::int, (random()*100)::int from
generate_series (1, 20000) as gs(i) ;
analyze test1 ;
/* between same columns */
explain select * from test1 where state1=state1 ;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test1 (cost=0.00..359.00 rows=100 width=16)
Filter: (state1 = state1)
(2 rows)
test3=# explain select * from test1 where state2=state2 ;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test1 (cost=0.00..359.00 rows=100 width=16)
Filter: (state2 = state2)
(2 rows)
/* between different columns of same table */
test3=# explain select * from test1 where state1=state2 ;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test1 (cost=0.00..359.00 rows=100 width=16)
Filter: (state1 = state2)
(2 rows)
===================================================================
/* create a table with random data and 100000 rows to verify */
create table test2 ( id int not null primary key, state1 int not null
default 0, state2 int not null default 0, state3 int not null default 0 );
insert into test2 (id, state1, state2, state3) select i,
(random()*3)::int, (random())::int, (random()*100)::int from
generate_series (1, 100000) as gs(i) ;
test3=# analyze test2 ;
ANALYZE
test3=# explain select * from test2 where
state1=state3; QUERY PLAN
-----------------------------------------------------------
Seq Scan on test2 (cost=0.00..1791.00 rows=500 width=16)
Filter: (state1 = state3)
(2 rows)
test3=# explain select * from test2 where state1=state2;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on test2 (cost=0.00..1791.00 rows=500 width=16)
Filter: (state1 = state2)
(2 rows)
test3=# explain select * from test2 where state1=state1;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on test2 (cost=0.00..1791.00 rows=500 width=16)
Filter: (state1 = state1)
(2 rows)
It's seems always 0.5% of the rows , and it seems indipendent of the type
of data you have in row :
/*add a column where costant value named c3 */
alter table test1 add c3 int default 1 ;
ALTER TABLE
analyze test1 ;
ANALYZE
explain select * from test1 where state1=c3;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test1 (cost=0.00..378.00 rows=100 width=20)
Filter: (state1 = c3)
(2 rows)
/*add a column where costant value named c3 */
alter table test2 add c3 int default 1 ;
ALTER TABLE
analyze test2 ;
ANALYZE
explain select * from test2 where state1=c3;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on test2 (cost=0.00..1887.00 rows=500 width=20)
Filter: (state1 = c3)
(2 rows)
/* add another constant column */
test3=# alter table test2 add c4 int default 1 ;
ALTER TABLE
test3=# analyze test2 ;
ANALYZE
test3=# explain select * from test2 where c3=c4 ;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on test2 (cost=0.00..1887.00 rows=500 width=24)
Filter: (c3 = c4)
obviously the statistics are ok :
Always 0.5%.
Greetings
Matteo
