Le 29 août 2014 18:13, "Emmanuel Medernach" <meder...@clermont.in2p3.fr> a écrit : > > Hello, > > I use Postgres version 9.3.5 and spot a performance issue > with postgres_fdw. > > I have a table object_003_xyz with 275000 lines and is > exported to the master node as master_object_003_xyz. > > ( The following query is only a part of an automatically > generated complex query. ) > > On the master: > > SELECT * FROM master_object_003_xyz AS o1, master_object_003_xyz AS o2 WHERE o1.objectid <> o2.objectid AND cos(radians(o1.ra_PS)) * cos(radians(o1.decl_PS)) BETWEEN cos(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND cos(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o1.ra_PS)) * cos(radians(o1.decl_PS)) BETWEEN sin(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o1.decl_PS)) BETWEEN sin(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND degrees(asin(sqrt(power(sin(radians((o2.decl_PS - o1.decl_PS) / 2)), 2) + power(sin(radians((o2.ra_PS - o1.ra_PS) / 2)), 2) * cos(radians(o1.decl_PS)) * cos(radians(o2.decl_PS)))) * 2) <= 1.5e-5 > (4 rows) > Time: 513711.684 ms > > Here is the plan used: > > Nested Loop (cost=200.70..44187032.64 rows=34518880 width=2168) > -> Foreign Scan on master_object_003_xyz o2 (cost=100.00..24294.47 rows=275449 width=1084) > -> Foreign Scan on master_object_003_xyz o1 (cost=100.70..160.32 rows=1 width=1084) > > > On the pool: > > SELECT * FROM object_003_xyz AS o1, object_003_xyz AS o2 WHERE o1.objectid <> o2.objectid AND cos(radians(o1.ra_PS)) * cos(radians(o1.decl_PS)) BETWEEN cos(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND cos(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o1.ra_PS)) * cos(radians(o1.decl_PS)) BETWEEN sin(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o2.ra_PS)) * cos(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o1.decl_PS)) BETWEEN sin(radians(o2.decl_PS)) - sin(radians(1.5e-5 / 2)) * 2 AND sin(radians(o2.decl_PS)) + sin(radians(1.5e-5 / 2)) * 2 AND > degrees(asin(sqrt(power(sin(radians((o2.decl_PS - o1.decl_PS) / 2)), 2) + power(sin(radians((o2.ra_PS - o1.ra_PS) / 2)), 2) * cos(radians(o1.decl_PS)) * cos(radians(o2.decl_PS)))) * 2) <= 1.5e-5 > (4 rows) > Time: 2738.217 ms > > It is much faster because it uses available index : > > Nested Loop (cost=0.56..360279717.93 rows=34692216 width=2168) > -> Seq Scan on object_003_xyz o2 (cost=0.00..18685.49 rows=275449 width=1084) > -> Index Scan using object_003_xyz_idx_xyz on object_003_xyz o1 (cost=0.56..1306.64 rows=126 width=1084) > Index Cond: (((cos(radians(ra_ps)) * cos(radians(decl_ps))) >= ((cos(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) - 2.61799387799149e-07::double precision)) AND ((cos(radians(ra_ps)) * cos(radians(decl_ps))) <= ((cos(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) + 2.61799387799149e-07::double precision)) AND ((sin(radians(ra_ps)) * cos(radians(decl_ps))) >= ((sin(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) - 2.61799387799149e-07::double precision)) AND ((sin(radians(ra_ps)) * cos(radians(decl_ps))) <= ((sin(radians(o2.ra_ps)) * cos(radians(o2.decl_ps))) + 2.61799387799149e-07::double precision)) AND (sin(radians(decl_ps)) >= (sin(radians(o2.decl_ps)) - 2.61799387799149e-07::double precision)) AND (sin(radians(decl_ps)) <= (sin(radians(o2.decl_ps)) + 2.61799387799149e-07::double precision))) > Filter: ((objectid <> o2.objectid) AND (degrees((asin(sqrt((power(sin(radians(((o2.decl_ps - decl_ps) / 2::double precision))), 2::double precision) + ((power(sin(radians(((o2.ra_ps - ra_ps) / 2::double precision))), 2::double precision) * cos(radians(decl_ps))) * cos(radians(o2.decl_ps)))))) * 2::double precision)) <= 1.5e-05::double precision)) > > > Would it be possible to avoid doing a nested loop of foreign > scans when dealing with tables on the same pool ? And to > automatically export the query directly in that case ? > > What may I do for now ? >
Not much. Joins are known to be a weak part of foreign tables right now.
