Обсуждение: Performance issue with postgres_fdw

Поиск
Список
Период
Сортировка

Performance issue with postgres_fdw

От
Emmanuel Medernach
Дата:
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 ?

Thanks for your help and best regards,
--
Emmanuel Medernach

Re: Performance issue with postgres_fdw

От
Guillaume Lelarge
Дата:

Le 29 août 2014 18:13, "Emmanuel Medernach" <medernac@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.