Search Postgresql Archives

strange nested loop row count estimates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi, 

I'm currently trying to understand the expected row counts for a query involving a nested loop join and bitmap index scan 
on the functional index and a custom operator. And the numbers that I see don't make sense to me currently. Hopefully 
somebody here can shed some light on it, or confirm this is some kind of issue.  

Here is the query and explain analyze

explain analyze select * from twomass.psc as t , gaia_dr2.gaia_source as g where 
	(
		(q3c_ang2ipix(g.ra,g.dec) between q3c_nearby_it(t.ra, t.decl, 0.0003, 0)  and  
						q3c_nearby_it(t.ra, t.decl, 0.0003, 1))  
	or  
		(q3c_ang2ipix(g.ra,g.dec) between q3c_nearby_it(t.ra, t.decl, 0.0003, 1)  and  
						q3c_nearby_it(t.ra, t.decl, 0.0003, 3))
	) 
	and
	0.0003 ==<<>>== (g.ra,g.dec,t.ra,t.decl)::q3c_type limit 10;

https://explain.depesz.com/s/vcNd

What I can't understand at all is how the estimate of 3E15 rows!!!! is obtained by the nested loop 
given that the bitmap heap scan is expected to return *one* single row for each row of the 'left' table. 
So in my mind the estimate of the total number of rows
should be ~ 1e9 rows after the nested loop. Because of this crazy overestimate, I actually have to force the nested loop 
in this query by disabling seqscan. 
(if I don't disable the seqscan -- this is the plan I get which ignores the indices:
https://explain.depesz.com/s/EIiG

Some more details about the query: 
q3c_ang2ipix(ra,dec) is the function mapping (double,double) -> bigint and the tables have a functional index on that.
Like this: 
                   Table "gaia_dr2.gaia_source"
              Column              |       Type        | Modifiers 
----------------------------------+-------------------+-----------
 ra                               | double precision  | 
 dec                              | double precision  | 
.......
Indexes:
    "gaia_source2_q3c_ang2ipix_idx" btree (q3c_ang2ipix(ra, "dec"))

q3c_nearby_() function just returns bigint.

The ==<<>== is the custom operator with custom low selectivity (1e-12 in this case)

The tables in the join in question have 450 mill  and 1.5 billion rows. 

I hope somebody can help me understand what's going on. 

Thank you in advance. 

  Sergey


PS the kind of query that I show  comes from the q3c module ( https://github.com/segasai/q3c ) 
that is used for spatial queries of large astronomical catalogues. 





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux