Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT: > Hello, > > No one to help me to understand this bad estimation rows ? Well, on a rather beefy machine, I'm getting quite a different plan: http://explain.depesz.com/s/3y5r Which may be related to this setting: perftest=# show default_statistics_target ; default_statistics_target --------------------------- 1000 (1 Zeile) I guess the wrong row assumption (which I get as well!) is caused by the given correlation of t3.c1 and t3.c2 (which the planner doesn't "see"). Tomas Vondra has written a nice blog post, covering that topic as well: http://blog.pgaddict.com/posts/common-issues-with-planner-statistics AFAIK, 9.5 has received some improvements in that field, but I didn't try that yet. Best regards, Nick > > Mathieu VINCENT > > 2015-12-11 12:35 GMT+01:00 Mathieu VINCENT > <mathieu.vincent@xxxxxxxxxxxxx <mailto:mathieu.vincent@xxxxxxxxxxxxx>>: > > Sorry, I forget to precise Postgresql version > > 'PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit' > > > BR > > Mathieu VINCENT > > > > > 2015-12-11 9:53 GMT+01:00 Mathieu VINCENT > <mathieu.vincent@xxxxxxxxxxxxx <mailto:mathieu.vincent@xxxxxxxxxxxxx>>: > > Hello, > > I would like to know how row estimation is calculed by explain ? > In my execution plan, this estimation is extremely wrong (267 > instead of 198000) > I reproduced this estimation error in this simple case : > > drop table if exists t1; > drop table if exists t2; > drop table if exists t3; > drop table if exists t4; > > create table t1 as select generate_Series(1,300000) as c1; > create table t2 as select generate_Series(1,400) as c1; > create table t3 as select generate_Series(1,200000)%100 as > c1,generate_Series(1,200000) as c2; > create table t4 as select generate_Series(1,200000) as c1; > > alter table t1 add PRIMARY KEY (c1); > alter table t2 add PRIMARY KEY (c1); > alter table t3 add PRIMARY KEY (c1,c2); > create index on t3 (c1); > create index on t3 (c2); > alter table t4 add PRIMARY KEY (c1); > > analyze t1; > analyze t2; > analyze t3; > analyze t4; > > EXPLAIN (analyze on, buffers on, verbose on) > select > * > from > t1 t1 > inner join t2 on t1.c1=t2.c1 > inner join t3 on t2.c1=t3.c1 > inner join t4 on t3.c2=t4.c1 > > Explain plan : > http://explain.depesz.com/s/wZ3v > > I think this error may be problematic because planner will > choose nested loop instead of hash joins for ultimate join. Can > you help me to improve this row estimation ? > > Thank you for answering > > Best Regards, > <http://www.psih.fr/> PSIH Décisionnel en santé > Mathieu VINCENT > Data Analyst > PMSIpilot - 61 rue Sully - 69006 Lyon - France > > > -- Gunnar "Nick" Bluth DBA ELSTER Tel: +49 911/991-4665 Mobil: +49 172/8853339 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
