Hi,
I have two plans of a query.
nestloop plan is much faster, but planner chose the slower one, hashjoin.
http://explain.depesz.com/s/Aqs http://explain.depesz.com/s/97Cit seems that rows=39698995 are quite overestimated.
-> Nested Loop (cost=0.000..5403.600 rows=39698995 width=45) (actual time=0.392..14.817 rows=943 loops=1)
-> Nested Loop (cost=0.000..17.600 rows=1 width=8) (actual time=0.241..0.246 rows=1 loops=1)
-> Index Scan using seven on hotel three (cost=0.000..6.880 rows=1 width=6) (actual time=0.113..0.115 rows=1 loops=1)
Index Cond: (two = 31750::numeric)
-> Index Scan using echo on oscar_foxtrot november (cost=0.000..10.710 rows=1 width=14) (actual time=0.117..0.118 rows=1 loops=1)
Index Cond: (charlie = three.golf)
-> Index Scan using zulu on oscar_foxtrot juliet (cost=0.000..3849.200 rows=153679 width=45) (actual time=0.147..14.241 rows=943 loops=1)
Index Cond: ((uniform_yankee = november.uniform_yankee) AND (uniform_victor = november.uniform_victor))
pg_stats is like this;
> select attname, null_frac, n_distinct, most_common_vals, most_common_freqs from pg_stats where tablename like 'oscar_foxtrot%' and (attname = 'uniform_yankee' or attname = 'uniform_victor')
"uniform_yankee";0;12;"{83886082,83886085}";"{0.9742,0.02}"
"uniform_victor";0;23;"{1342767106,1342308357}";"{0.973467,0.02}"
I assumed that nestloop rows would be more or less inner_path_rows * outer_path_rows with good pg_stats, and good plan could come based on it.
the plan above is not that case. Suspcious of 40 million rows and small number of values(actually two values) making up 98% of distribution.
so.. I looked up some code and found that rows=153679 is rows of parameterized base rel estimated by eqsel(), and row=39698995 is rows of parameterized join rel by eqjoinsel().
I think wrong plan above comes from the fact that the two estimation cannot be close in general, great difference in my case.
where am i wrong and right?
Is there recommended approach, related issue, commit and so on i can follow ?
thanks
[[nstallation Info]]
PostgreSQL-9.2.5 (via postgresql yum repository)
OS: Centos 6.3 (custom linux-3.10.12 kernel)
postgresql.conf:
effective_cache_size = 10000MB
shared_buffers = 1000MB
work_mem = 100MB
maintenance_work_mem = 100MB
HW: CPU 4-core Xeon x 2 sockets, RAM 256GB
--
Regards,
Jang.
a sound mind in a sound body
