<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">I have four tables: grids contains grid of polygon, routes contains gps point survey and segment between a point and the next, rank_type, and parameters_queries.<br>I try to intersect a grid from grids with some segments of routes thru ST_INTERSECTS.<br><br>This is the first query i tried:<br><br>SELECT grd.coordinates_system<br> , polygon<br> , parameter<br> , lon_index<br> , lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> , quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> ,
lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> , quota_north_west<br> , cell<br> , COALESCE(SUM(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort<br> , COALESCE(AVG(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_mean<br> , COALESCE(STDDEV(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_devstd<br> , COALESCE(VARIANCE(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_var<br>from<br>(<br>SELECT pq1.legenda<br> ,
pq2.min AS period_date_min<br> , pq2.max AS period_date_max<br> , pq3.min AS workstation_time_min<br> ,
pq3.max AS workstation_time_max<br> , pq4.min AS week_date_min<br> , pq4.max AS week_date_max<br> ,
pq5.min AS rank_min<br> , pq5.max AS rank_max<br> , pq6.min AS speed_min<br> ,
pq6.max AS speed_max<br> , pq7.min AS gps_time_min<br> , pq7.max AS gps_time_max<br> ,
pq8.min AS rank_type_min<br> , pq8.max AS rank_type_max<br>FROM parameters_queries pq1<br> , parameters_queries pq2<br> ,
parameters_queries pq3<br> , parameters_queries pq4<br> , parameters_queries pq5<br> , parameters_queries pq6<br> ,
parameters_queries pq7<br> , parameters_queries pq8<br>WHERE pq1.tag::text = 'COORDSYS'::text <br>AND pq2.tag::text = 'ROUTES'::text <br>AND
pq2.legenda::text = 'PERIOD'::text <br>AND pq3.tag::text = 'ROUTES'::text <br>AND pq3.legenda::text = 'WORKSTATIONTIME'::text <br>AND pq4.tag::text = 'ROUTES'::text <br>AND
pq4.legenda::text = 'WEEK'::text <br>AND pq5.tag::text = 'ROUTES'::text <br>AND pq5.legenda::text = 'RANK'::text <br>AND pq6.tag::text = 'ROUTES'::text <br>AND
pq6.legenda::text = 'SPEED'::text <br>AND pq7.tag::text = 'ROUTES'::text <br>AND pq7.legenda::text = 'GPSTIME'::text <br>AND pq8.tag::text = 'ROUTES'::text <br>AND
pq8.legenda::text = 'RANKTYPE'::text <br>) pq, <br>(<br>SELECT routes.rank<br> , routes.period_date<br> , routes.workstation_time<br> , routes.gps_time<br> , routes.week_date<br> , routes.survey_date<br> , routes.geo_point<br> , routes.lon<br> , routes.lat<br> , routes.quota<br> , routes.y<br> , routes.x<br> , routes.z<br> , routes.speed<br> , routes.direction<br> ,
routes.segment_utm_wgs84 AS segment <br>FROM routes<br>WHERE geo_point > 90000000<br>) rou<br>right outer join<br>(<br>SELECT pq1.legenda AS coordinates_system<br> , polygon<br> , cell_length<br> , cells_side<br> , grd.parameter AS parameter<br> , lon_index<br> , lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> ,
quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> , lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> , quota_north_west<br> , grd.polygon_utm_wgs84 AS cell <br>FROM delphis.grids grd <br> , delphis.parameters_grid pgr <br> , delphis.parameters_queries pq1<br> , delphis.parameters_queries
pq2<br> , delphis.parameters_queries pq3<br> , delphis.parameters_queries pq4<br>WHERE pq1.tag::text = 'COORDSYS'::text<br>AND pq2.tag::text = 'GRID02'::text<br>AND pq2.legenda::text = 'CELL_LENGTH'::text<br>AND pq3.tag::text = 'GRID02'::text<br>AND pq3.legenda::text = 'LOW_LEFT_CORNER'::text<br>AND pq4.tag::text =
'GRID02'::text<br>AND pq4.legenda::text = 'HIGH_RIGHT_CORNER'::text<br>AND pgr.cell_length = pq2.min<br>AND grd.parameter = pgr.parameter<br>AND grd.polygon_utm_wgs84 && ST_MAKEBOX3D(ST_SETSRID(ST_MAKEPOINT(pq3.min,pq3.max,pq3.factor),32633)<br>
,ST_SETSRID(ST_MAKEPOINT(pq4.min,pq4.max,pq4.factor),32633)<br> ) <br>) grd<br>ON ST_INTERSECTS(segment,cell)<br>GROUP <br>BY grd.coordinates_system<br> , polygon<br> , cell_length<br> , cells_side<br> , parameter<br> , lon_index<br> ,
lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> , quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> , lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> , quota_north_west<br> , cell<br>ORDER<br>BY polygon;<br><br>subquery named pq returns 1 row<br>subquery named rou returns 17274 rows <br>subquery named grd returns 8281 rows<br><br>of course we have spatial index on segment and cell, and pk on polygon, geo_point<br><br>this is explain
plan:<br><br>"GroupAggregate (cost=33.02..34.12 rows=1 width=1054) (actual time=27339.441..35239.856 rows=8281 loops=1)"<br>" -> Sort (cost=33.02..33.03 rows=1 width=1054) (actual time=27339.387..27412.462 rows=30857 loops=1)"<br>" Sort Key: grd.polygon, pq1.legenda, pgr.cell_length, pgr.cells_side, grd.parameter, grd.lon_index, grd.lat_index, grd.quota_index, grd.lon_south_west, grd.lat_south_west, grd.quota_south_west, grd.lon_south_east, grd.lat_south_east, grd.quota_south_east, grd.lon_north_east, grd.lat_north_east, grd.quota_north_east, grd.lon_north_west, grd.lat_north_west, grd.quota_north_west, grd.polygon_utm_wgs84"<br>" Sort Method: external merge Disk: 10824kB"<br>" -> Nested Loop (cost=0.02..33.01 rows=1 width=1054) (actual time=98.144..26683.853 rows=30857
loops=1)"<br>" Join Filter: (pq2.min = (pgr.cell_length)::double precision)"<br>" -> Nested Loop (cost=0.02..32.73 rows=1 width=1054) (actual time=2.358..25125.270 rows=184769 loops=1)"<br>" -> Nested Loop Left Join (cost=0.02..30.76 rows=1 width=1046) (actual time=2.328..21115.637 rows=184769 loops=1)"<br>" Join Filter: _st_intersects(routes.segment_utm_wgs84,
grd.polygon_utm_wgs84)"<br>" -> Nested Loop (cost=0.02..29.74 rows=1 width=738) (actual time=2.011..456.911 rows=81964 loops=1)"<br>" -> Nested Loop (cost=0.00..21.34 rows=1 width=58) (actual time=0.362..0.494 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..19.37 rows=1 width=34) (actual time=0.342..0.457 rows=1
loops=1)"<br>" -> Nested Loop (cost=0.00..17.40 rows=1 width=10) (actual time=0.301..0.408 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..15.59 rows=1 width=0) (actual time=0.280..0.373 rows=1
loops=1)"<br>" -> Nested Loop (cost=0.00..13.78 rows=1 width=0) (actual time=0.260..0.338 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..11.81 rows=1 width=0) (actual time=0.235..0.299 rows=1
loops=1)"<br>" -> Nested Loop (cost=0.00..9.84 rows=1 width=0) (actual time=0.218..0.263 rows=1 loops=1)"<br>"
-> Nested Loop (cost=0.00..7.87 rows=1 width=0) (actual time=0.176..0.216 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..5.90 rows=1 width=0) (actual time=0.148..0.176 rows=1
loops=1)"<br>" -> Nested Loop (cost=0.00..3.93 rows=1 width=0) (actual time=0.106..0.126 rows=1
loops=1)"<br>" -> Seq Scan on parameters_queries pq2 (cost=0.00..1.96 rows=1 width=0) (actual time=0.071..0.078 rows=1
loops=1)"<br>" Filter: (((tag)::text = 'ROUTES'::text) AND ((legenda)::text =
'PERIOD'::text))"<br>" -> Seq Scan on parameters_queries pq8 (cost=0.00..1.96 rows=1 width=0) (actual time=0.023..0.033 rows=1
loops=1)"<br>" Filter: (((pq8.tag)::text = 'ROUTES'::text) AND ((pq8.legenda)::text =
'RANKTYPE'::text))"<br>" -> Seq Scan on parameters_queries pq7 (cost=0.00..1.96 rows=1 width=0) (actual time=0.037..0.039 rows=1
loops=1)"<br>" Filter: (((pq7.tag)::text = 'ROUTES'::text) AND ((pq7.legenda)::text =
'GPSTIME'::text))"<br>" -> Seq Scan on parameters_queries pq6 (cost=0.00..1.96 rows=1 width=0) (actual time=0.022..0.032 rows=1
loops=1)"<br>" Filter: (((pq6.tag)::text = 'ROUTES'::text) AND ((pq6.legenda)::text =
'SPEED'::text))"<br>" -> Seq Scan on parameters_queries pq5 (cost=0.00..1.96 rows=1 width=0) (actual time=0.037..0.038 rows=1
loops=1)"<br>" Filter: (((pq5.tag)::text = 'ROUTES'::text) AND ((pq5.legenda)::text =
'RANK'::text))"<br>" -> Seq Scan on parameters_queries pq4 (cost=0.00..1.96 rows=1 width=0) (actual time=0.011..0.025 rows=1
loops=1)"<br>" Filter: (((pq4.tag)::text = 'ROUTES'::text) AND ((pq4.legenda)::text = 'WEEK'::text))"<br>" -> Seq Scan on parameters_queries pq3
(cost=0.00..1.96 rows=1 width=0) (actual time=0.020..0.030 rows=1 loops=1)"<br>" Filter: (((pq3.tag)::text = 'ROUTES'::text) AND ((pq3.legenda)::text = 'WORKSTATIONTIME'::text))"<br>" -> Seq Scan on parameters_queries
pq1 (cost=0.00..1.80 rows=1 width=0) (actual time=0.014..0.025 rows=1 loops=1)"<br>" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"<br>" -> Seq Scan on parameters_queries pq1 (cost=0.00..1.80 rows=1 width=10) (actual time=0.015..0.025 rows=1
loops=1)"<br>" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"<br>" -> Seq Scan on parameters_queries pq4 (cost=0.00..1.96 rows=1 width=24) (actual time=0.034..0.038 rows=1
loops=1)"<br>" Filter: (((pq4.tag)::text = 'GRID02'::text) AND ((pq4.legenda)::text = 'HIGH_RIGHT_CORNER'::text))"<br>" -> Seq Scan on parameters_queries pq3 (cost=0.00..1.96 rows=1 width=24) (actual time=0.013..0.026 rows=1
loops=1)"<br>" Filter: (((pq3.tag)::text = 'GRID02'::text) AND ((pq3.legenda)::text = 'LOW_LEFT_CORNER'::text))"<br>" -> Index Scan using polygon_utm_wgs84_idx on grids grd (cost=0.02..8.37 rows=1 width=728) (actual time=1.560..259.897 rows=81964 loops=1)"<br>" Index Cond:
(grd.polygon_utm_wgs84 && (st_makebox3d(st_setsrid(st_makepoint(pq3.min, pq3.max, pq3.factor), 32633), st_setsrid(st_makepoint(pq4.min, pq4.max, pq4.factor), 32633)))::geometry)"<br>" -> Index Scan using idx_segment_utm_wgs84 on routes (cost=0.00..0.75 rows=1 width=308) (actual time=0.149..0.227 rows=3 loops=81964)"<br>" Index Cond: (routes.segment_utm_wgs84 && grd.polygon_utm_wgs84)"<br>" Filter:
(routes.geo_point > 90000000)"<br>" -> Seq Scan on parameters_queries pq2 (cost=0.00..1.96 rows=1 width=8) (actual time=0.004..0.016 rows=1 loops=184769)"<br>" Filter: (((pq2.tag)::text = 'GRID02'::text) AND ((pq2.legenda)::text = 'CELL_LENGTH'::text))"<br>" -> Index Scan using parameters_grid_pkey on parameters_grid pgr (cost=0.00..0.27 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=184769)"<br>" Index Cond: (pgr.parameter = grd.parameter)"<br>"Total runtime:
35254.012 ms"<br><br>
35s not so fast but is still human time to wait.....<br><br>Now i want add a natural join to subquery named rou on other table rank_type that it has only 26 rows.....<br><br>SELECT grd.coordinates_system<br> , polygon<br> , parameter<br> , lon_index<br> , lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> , quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> , lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> ,
quota_north_west<br> , cell<br> , COALESCE(SUM(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort<br> , COALESCE(AVG(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_mean<br> , COALESCE(STDDEV(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_devstd<br> , COALESCE(VARIANCE(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_var<br>from<br>(<br>SELECT pq1.legenda<br> , pq2.min AS period_date_min<br> ,
pq2.max AS period_date_max<br> , pq3.min AS workstation_time_min<br> , pq3.max AS workstation_time_max<br> ,
pq4.min AS week_date_min<br> , pq4.max AS week_date_max<br> , pq5.min AS rank_min<br> ,
pq5.max AS rank_max<br> , pq6.min AS speed_min<br> , pq6.max AS speed_max<br> ,
pq7.min AS gps_time_min<br> , pq7.max AS gps_time_max<br> , pq8.min AS rank_type_min<br> ,
pq8.max AS rank_type_max<br>FROM parameters_queries pq1<br> , parameters_queries pq2<br> , parameters_queries pq3<br> ,
parameters_queries pq4<br> , parameters_queries pq5<br> , parameters_queries pq6<br> , parameters_queries pq7<br> ,
parameters_queries pq8<br>WHERE pq1.tag::text = 'COORDSYS'::text <br>AND pq2.tag::text = 'ROUTES'::text <br>AND pq2.legenda::text = 'PERIOD'::text <br>AND
pq3.tag::text = 'ROUTES'::text <br>AND pq3.legenda::text = 'WORKSTATIONTIME'::text <br>AND pq4.tag::text = 'ROUTES'::text <br>AND pq4.legenda::text = 'WEEK'::text <br>AND
pq5.tag::text = 'ROUTES'::text <br>AND pq5.legenda::text = 'RANK'::text <br>AND pq6.tag::text = 'ROUTES'::text <br>AND pq6.legenda::text = 'SPEED'::text <br>AND
pq7.tag::text = 'ROUTES'::text <br>AND pq7.legenda::text = 'GPSTIME'::text <br>AND pq8.tag::text = 'ROUTES'::text <br>AND pq8.legenda::text = 'RANKTYPE'::text <br>) pq, <br>(<br>SELECT
routes.rank<br> , routes.period_date<br> , routes.workstation_time<br> , routes.gps_time<br> , routes.week_date<br> , routes.survey_date<br> , routes.geo_point<br> , routes.lon<br> , routes.lat<br> , routes.quota<br> , routes.y<br> , routes.x<br> , routes.z<br> , routes.speed<br> , routes.direction<br> , routes.segment_utm_wgs84 AS segment <br>FROM routes<br> ,
rank_type <br>WHERE routes.rank_type = rank_type.rank_type <br>AND geo_point > 90000000<br>) rou<br>right outer join<br>(<br>SELECT pq1.legenda AS coordinates_system<br> , polygon<br> , cell_length<br> , cells_side<br> , grd.parameter AS parameter<br> ,
lon_index<br> , lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> , quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> , lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> , quota_north_west<br> , grd.polygon_utm_wgs84 AS cell <br>FROM delphis.grids grd <br> ,
delphis.parameters_grid pgr <br> , delphis.parameters_queries pq1<br> , delphis.parameters_queries pq2<br> , delphis.parameters_queries pq3<br> , delphis.parameters_queries pq4<br>WHERE pq1.tag::text = 'COORDSYS'::text<br>AND pq2.tag::text = 'GRID02'::text<br>AND pq2.legenda::text = 'CELL_LENGTH'::text<br>AND pq3.tag::text =
'GRID02'::text<br>AND pq3.legenda::text = 'LOW_LEFT_CORNER'::text<br>AND pq4.tag::text = 'GRID02'::text<br>AND pq4.legenda::text = 'HIGH_RIGHT_CORNER'::text<br>AND pgr.cell_length = pq2.min<br>AND grd.parameter = pgr.parameter<br>AND grd.polygon_utm_wgs84 &&
ST_MAKEBOX3D(ST_SETSRID(ST_MAKEPOINT(pq3.min,pq3.max,pq3.factor),32633)<br> ,ST_SETSRID(ST_MAKEPOINT(pq4.min,pq4.max,pq4.factor),32633)<br> ) <br>)
grd<br>ON ST_INTERSECTS(segment,cell)<br>GROUP <br>BY grd.coordinates_system<br> , polygon<br> , cell_length<br> , cells_side<br> , parameter<br> , lon_index<br> , lat_index<br> , quota_index<br> , lon_south_west<br> , lat_south_west<br> , quota_south_west<br> , lon_south_east<br> , lat_south_east<br> , quota_south_east<br> , lon_north_east<br> , lat_north_east<br> , quota_north_east<br> , lon_north_west<br> , lat_north_west<br> ,
quota_north_west<br> , cell<br>ORDER<br>BY polygon;<br> <br>
So this is the explain plan:<br><br>"GroupAggregate (cost=14064.20..14065.30 rows=1 width=1054) (actual time=8239531.621..8247508.994 rows=8281 loops=1)"<br>" -> Sort (cost=14064.20..14064.21 rows=1 width=1054) (actual time=8239531.566..8239600.772 rows=30857 loops=1)"<br>" Sort Key: grd.polygon, pq1.legenda, pgr.cell_length, pgr.cells_side, grd.parameter, grd.lon_index, grd.lat_index, grd.quota_index, grd.lon_south_west, grd.lat_south_west, grd.quota_south_west, grd.lon_south_east, grd.lat_south_east, grd.quota_south_east, grd.lon_north_east, grd.lat_north_east, grd.quota_north_east, grd.lon_north_west, grd.lat_north_west, grd.quota_north_west, grd.polygon_utm_wgs84"<br>" Sort Method: external merge Disk: 10824kB"<br>" -> Nested Loop (cost=683.79..14064.19 rows=1 width=1054)
(actual time=48511.602..8238741.620 rows=30857 loops=1)"<br>" -> Nested Loop (cost=0.00..15.59 rows=1 width=0) (actual time=0.254..0.346 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..13.78 rows=1 width=0) (actual time=0.234..0.312 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..11.81 rows=1 width=0) (actual time=0.209..0.273 rows=1 loops=1)"<br>" -> Nested Loop
(cost=0.00..9.84 rows=1 width=0) (actual time=0.193..0.239 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..7.87 rows=1 width=0) (actual time=0.152..0.193 rows=1 loops=1)"<br>" -> Nested Loop (cost=0.00..5.90 rows=1 width=0) (actual time=0.127..0.154 rows=1
loops=1)"<br>" -> Nested Loop (cost=0.00..3.93 rows=1 width=0) (actual time=0.086..0.106 rows=1 loops=1)"<br>" -> Seq Scan on parameters_queries pq2 (cost=0.00..1.96 rows=1 width=0) (actual time=0.056..0.063 rows=1
loops=1)"<br>" Filter: (((tag)::text = 'ROUTES'::text) AND ((legenda)::text = 'PERIOD'::text))"<br>" -> Seq Scan on parameters_queries pq8 (cost=0.00..1.96 rows=1 width=0) (actual time=0.021..0.031 rows=1
loops=1)"<br>" Filter: (((pq8.tag)::text = 'ROUTES'::text) AND ((pq8.legenda)::text = 'RANKTYPE'::text))"<br>" -> Seq Scan on parameters_queries pq7 (cost=0.00..1.96 rows=1 width=0) (actual time=0.036..0.039 rows=1
loops=1)"<br>" Filter: (((pq7.tag)::text = 'ROUTES'::text) AND ((pq7.legenda)::text = 'GPSTIME'::text))"<br>" -> Seq Scan on parameters_queries pq6 (cost=0.00..1.96 rows=1 width=0) (actual time=0.021..0.030 rows=1
loops=1)"<br>" Filter: (((pq6.tag)::text = 'ROUTES'::text) AND ((pq6.legenda)::text = 'SPEED'::text))"<br>" -> Seq Scan on parameters_queries pq5 (cost=0.00..1.96 rows=1 width=0) (actual time=0.037..0.039 rows=1
loops=1)"<br>" Filter: (((pq5.tag)::text = 'ROUTES'::text) AND ((pq5.legenda)::text = 'RANK'::text))"<br>" -> Seq Scan on parameters_queries pq4 (cost=0.00..1.96 rows=1 width=0) (actual time=0.011..0.026 rows=1 loops=1)"<br>" Filter: (((pq4.tag)::text = 'ROUTES'::text) AND
((pq4.legenda)::text = 'WEEK'::text))"<br>" -> Seq Scan on parameters_queries pq3 (cost=0.00..1.96 rows=1 width=0) (actual time=0.019..0.029 rows=1 loops=1)"<br>" Filter: (((pq3.tag)::text = 'ROUTES'::text) AND ((pq3.legenda)::text = 'WORKSTATIONTIME'::text))"<br>" -> Seq Scan on parameters_queries pq1 (cost=0.00..1.80 rows=1 width=0) (actual time=0.015..0.025 rows=1
loops=1)"<br>" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"<br>" -> Nested Loop (cost=683.79..14048.59 rows=1 width=1054) (actual time=48511.340..8238655.685 rows=30857 loops=1)"<br>" -> Nested Loop (cost=683.79..14046.78 rows=1 width=1044) (actual time=48511.320..8238030.935 rows=30857 loops=1)"<br>" Join Filter: (pq2.min = (pgr.cell_length)::double
precision)"<br>" -> Nested Loop (cost=683.79..14046.50 rows=1 width=1044) (actual time=159.018..8235211.657 rows=184769 loops=1)"<br>" -> Nested Loop Left Join (cost=683.79..14044.53 rows=1 width=1036) (actual time=158.985..8229965.137 rows=184769 loops=1)"<br>" Join Filter: ((routes.segment_utm_wgs84 && grd.polygon_utm_wgs84) AND
_st_intersects(routes.segment_utm_wgs84, grd.polygon_utm_wgs84))"<br>" -> Nested Loop (cost=0.02..12.33 rows=1 width=728) (actual time=0.359..849.907 rows=81964 loops=1)"<br>" -> Nested Loop (cost=0.00..3.93 rows=1 width=48) (actual time=0.071..0.093 rows=1
loops=1)"<br>" -> Seq Scan on parameters_queries pq3 (cost=0.00..1.96 rows=1 width=24) (actual time=0.013..0.026 rows=1 loops=1)"<br>" Filter: (((tag)::text = 'GRID02'::text) AND ((legenda)::text =
'LOW_LEFT_CORNER'::text))"<br>" -> Seq Scan on parameters_queries pq4 (cost=0.00..1.96 rows=1 width=24) (actual time=0.052..0.056 rows=1 loops=1)"<br>" Filter: (((pq4.tag)::text = 'GRID02'::text) AND ((pq4.legenda)::text =
'HIGH_RIGHT_CORNER'::text))"<br>" -> Index Scan using polygon_utm_wgs84_idx on grids grd (cost=0.02..8.37 rows=1 width=728) (actual time=0.233..604.400 rows=81964 loops=1)"<br>" Index Cond: (grd.polygon_utm_wgs84 && (st_makebox3d(st_setsrid(st_makepoint(pq3.min, pq3.max, pq3.factor), 32633), st_setsrid(st_makepoint(pq4.min, pq4.max, pq4.factor),
32633)))::geometry)"<br>" -> Hash Join (cost=683.78..9633.75 rows=16756 width=308) (actual time=2.583..77.226 rows=17274 loops=81964)"<br>" Hash Cond: (routes.rank_type = rank_type.rank_type)"<br>" ->
Bitmap Heap Scan on routes (cost=682.15..9401.72 rows=16756 width=312) (actual time=2.578..25.357 rows=17274 loops=81964)"<br>" Recheck Cond: (geo_point > 90000000)"<br>" -> Bitmap Index Scan on routes_pkey (cost=0.00..677.96 rows=16756 width=0) (actual time=2.388..2.388 rows=17274
loops=81964)"<br>" Index Cond: (geo_point > 90000000)"<br>" -> Hash (cost=1.28..1.28 rows=28 width=4) (actual time=0.119..0.119 rows=26
loops=1)"<br>" -> Seq Scan on rank_type (cost=0.00..1.28 rows=28 width=4) (actual time=0.029..0.067 rows=26 loops=1)"<br>" -> Seq Scan on parameters_queries pq2 (cost=0.00..1.96 rows=1 width=8) (actual time=0.007..0.020 rows=1
loops=184769)"<br>" Filter: (((pq2.tag)::text = 'GRID02'::text) AND ((pq2.legenda)::text = 'CELL_LENGTH'::text))"<br>" -> Index Scan using parameters_grid_pkey on parameters_grid pgr (cost=0.00..0.27 rows=1 width=12) (actual time=0.007..0.009 rows=1 loops=184769)"<br>" Index Cond: (pgr.parameter =
grd.parameter)"<br>" -> Seq Scan on parameters_queries pq1 (cost=0.00..1.80 rows=1 width=10) (actual time=0.006..0.015 rows=1 loops=30857)"<br>" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"<br>"Total runtime: 8247522.985 ms"<br><br> 2 hours and twenty minutes to add a simple natural join in a subquery.....<br><br>Am i make a some stupid mistaken or is there some bugs ?<br><br></td></tr></table><br>