Fixed Cardinality estimation with equality predicates between column of the same table
От | desmodemone |
---|---|
Тема | Fixed Cardinality estimation with equality predicates between column of the same table |
Дата | |
Msg-id | CAEs9oFm7rxozOKDym8bAgyOUAJ7=TJRkihL3_anYThu0HZqWAQ@mail.gmail.com обсуждение исходный текст |
Список | 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) ;
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)
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Christopher BrowneДата:
Сообщение: Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks)
Следующее
От: Tom LaneДата:
Сообщение: Re: Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks)