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.
