From: | desmodemone <desmodemone(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Fixed Cardinality estimation with equality predicates between column of the same table |
Date: | 2013-06-21 21:33:23 |
Message-ID: | CAEs9oFm7rxozOKDym8bAgyOUAJ7=TJRkihL3_anYThu0HZqWAQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-06-21 21:44:21 | Re: Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks) |
Previous Message | Christopher Browne | 2013-06-21 21:03:18 | Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks) |