[postgis-users] ST_INTERSECTS crazy bad performances
marcello marcellone
nasoblu5 at yahoo.it
Mon Mar 15 09:33:02 PDT 2010
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.
I try to intersect a grid from grids with some segments of routes thru ST_INTERSECTS.
This is the first query i tried:
SELECT grd.coordinates_system
, polygon
, parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, cell
, COALESCE(SUM(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort
, COALESCE(AVG(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_mean
, COALESCE(STDDEV(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_devstd
, COALESCE(VARIANCE(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_var
from
(
SELECT pq1.legenda
, pq2.min AS period_date_min
, pq2.max AS period_date_max
, pq3.min AS workstation_time_min
, pq3.max AS workstation_time_max
, pq4.min AS week_date_min
, pq4.max AS week_date_max
, pq5.min AS rank_min
, pq5.max AS rank_max
, pq6.min AS speed_min
, pq6.max AS speed_max
, pq7.min AS gps_time_min
, pq7.max AS gps_time_max
, pq8.min AS rank_type_min
, pq8.max AS rank_type_max
FROM parameters_queries pq1
, parameters_queries pq2
, parameters_queries pq3
, parameters_queries pq4
, parameters_queries pq5
, parameters_queries pq6
, parameters_queries pq7
, parameters_queries pq8
WHERE pq1.tag::text = 'COORDSYS'::text
AND pq2.tag::text = 'ROUTES'::text
AND pq2.legenda::text = 'PERIOD'::text
AND pq3.tag::text = 'ROUTES'::text
AND pq3.legenda::text = 'WORKSTATIONTIME'::text
AND pq4.tag::text = 'ROUTES'::text
AND pq4.legenda::text = 'WEEK'::text
AND pq5.tag::text = 'ROUTES'::text
AND pq5.legenda::text = 'RANK'::text
AND pq6.tag::text = 'ROUTES'::text
AND pq6.legenda::text = 'SPEED'::text
AND pq7.tag::text = 'ROUTES'::text
AND pq7.legenda::text = 'GPSTIME'::text
AND pq8.tag::text = 'ROUTES'::text
AND pq8.legenda::text = 'RANKTYPE'::text
) pq,
(
SELECT routes.rank
, routes.period_date
, routes.workstation_time
, routes.gps_time
, routes.week_date
, routes.survey_date
, routes.geo_point
, routes.lon
, routes.lat
, routes.quota
, routes.y
, routes.x
, routes.z
, routes.speed
, routes.direction
, routes.segment_utm_wgs84 AS segment
FROM routes
WHERE geo_point > 90000000
) rou
right outer join
(
SELECT pq1.legenda AS coordinates_system
, polygon
, cell_length
, cells_side
, grd.parameter AS parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, grd.polygon_utm_wgs84 AS cell
FROM delphis.grids grd
, delphis.parameters_grid pgr
, delphis.parameters_queries pq1
, delphis.parameters_queries pq2
, delphis.parameters_queries pq3
, delphis.parameters_queries pq4
WHERE pq1.tag::text = 'COORDSYS'::text
AND pq2.tag::text = 'GRID02'::text
AND pq2.legenda::text = 'CELL_LENGTH'::text
AND pq3.tag::text = 'GRID02'::text
AND pq3.legenda::text = 'LOW_LEFT_CORNER'::text
AND pq4.tag::text = 'GRID02'::text
AND pq4.legenda::text = 'HIGH_RIGHT_CORNER'::text
AND pgr.cell_length = pq2.min
AND grd.parameter = pgr.parameter
AND 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)
)
) grd
ON ST_INTERSECTS(segment,cell)
GROUP
BY grd.coordinates_system
, polygon
, cell_length
, cells_side
, parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, cell
ORDER
BY polygon;
subquery named pq returns 1 row
subquery named rou returns 17274 rows
subquery named grd returns 8281 rows
of course we have spatial index on segment and cell, and pk on polygon, geo_point
this is explain plan:
"GroupAggregate (cost=33.02..34.12 rows=1 width=1054) (actual time=27339.441..35239.856 rows=8281 loops=1)"
" -> Sort (cost=33.02..33.03 rows=1 width=1054) (actual time=27339.387..27412.462 rows=30857 loops=1)"
" 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"
" Sort Method: external merge Disk: 10824kB"
" -> Nested Loop (cost=0.02..33.01 rows=1 width=1054) (actual time=98.144..26683.853 rows=30857 loops=1)"
" Join Filter: (pq2.min = (pgr.cell_length)::double precision)"
" -> Nested Loop (cost=0.02..32.73 rows=1 width=1054) (actual time=2.358..25125.270 rows=184769 loops=1)"
" -> Nested Loop Left Join (cost=0.02..30.76 rows=1 width=1046) (actual time=2.328..21115.637 rows=184769 loops=1)"
" Join Filter: _st_intersects(routes.segment_utm_wgs84, grd.polygon_utm_wgs84)"
" -> Nested Loop (cost=0.02..29.74 rows=1 width=738) (actual time=2.011..456.911 rows=81964 loops=1)"
" -> Nested Loop (cost=0.00..21.34 rows=1 width=58) (actual time=0.362..0.494 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..19.37 rows=1 width=34) (actual time=0.342..0.457 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..17.40 rows=1 width=10) (actual time=0.301..0.408 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..15.59 rows=1 width=0) (actual time=0.280..0.373 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..13.78 rows=1 width=0) (actual time=0.260..0.338 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..11.81 rows=1 width=0) (actual time=0.235..0.299 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..9.84 rows=1 width=0) (actual time=0.218..0.263 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..7.87 rows=1 width=0) (actual time=0.176..0.216 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..5.90 rows=1 width=0) (actual time=0.148..0.176 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..3.93 rows=1 width=0) (actual time=0.106..0.126 rows=1 loops=1)"
" -> 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)"
" Filter: (((tag)::text = 'ROUTES'::text) AND ((legenda)::text = 'PERIOD'::text))"
" -> 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)"
" Filter: (((pq8.tag)::text = 'ROUTES'::text) AND ((pq8.legenda)::text = 'RANKTYPE'::text))"
" -> 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)"
" Filter: (((pq7.tag)::text = 'ROUTES'::text) AND ((pq7.legenda)::text = 'GPSTIME'::text))"
" -> 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)"
" Filter: (((pq6.tag)::text = 'ROUTES'::text) AND ((pq6.legenda)::text = 'SPEED'::text))"
" -> 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)"
" Filter: (((pq5.tag)::text = 'ROUTES'::text) AND ((pq5.legenda)::text = 'RANK'::text))"
" -> 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)"
" Filter: (((pq4.tag)::text = 'ROUTES'::text) AND ((pq4.legenda)::text = 'WEEK'::text))"
" -> 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)"
" Filter: (((pq3.tag)::text = 'ROUTES'::text) AND ((pq3.legenda)::text = 'WORKSTATIONTIME'::text))"
" -> 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)"
" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"
" -> 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)"
" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"
" -> 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)"
" Filter: (((pq4.tag)::text = 'GRID02'::text) AND ((pq4.legenda)::text = 'HIGH_RIGHT_CORNER'::text))"
" -> 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)"
" Filter: (((pq3.tag)::text = 'GRID02'::text) AND ((pq3.legenda)::text = 'LOW_LEFT_CORNER'::text))"
" -> 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)"
" 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)"
" -> 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)"
" Index Cond: (routes.segment_utm_wgs84 && grd.polygon_utm_wgs84)"
" Filter: (routes.geo_point > 90000000)"
" -> 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)"
" Filter: (((pq2.tag)::text = 'GRID02'::text) AND ((pq2.legenda)::text = 'CELL_LENGTH'::text))"
" -> 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)"
" Index Cond: (pgr.parameter = grd.parameter)"
"Total runtime: 35254.012 ms"
35s not so fast but is still human time to wait.....
Now i want add a natural join to subquery named rou on other table rank_type that it has only 26 rows.....
SELECT grd.coordinates_system
, polygon
, parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, cell
, COALESCE(SUM(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort
, COALESCE(AVG(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_mean
, COALESCE(STDDEV(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_devstd
, COALESCE(VARIANCE(ST_length3d(ST_INTERSECTION(segment,cell))),0) AS effort_var
from
(
SELECT pq1.legenda
, pq2.min AS period_date_min
, pq2.max AS period_date_max
, pq3.min AS workstation_time_min
, pq3.max AS workstation_time_max
, pq4.min AS week_date_min
, pq4.max AS week_date_max
, pq5.min AS rank_min
, pq5.max AS rank_max
, pq6.min AS speed_min
, pq6.max AS speed_max
, pq7.min AS gps_time_min
, pq7.max AS gps_time_max
, pq8.min AS rank_type_min
, pq8.max AS rank_type_max
FROM parameters_queries pq1
, parameters_queries pq2
, parameters_queries pq3
, parameters_queries pq4
, parameters_queries pq5
, parameters_queries pq6
, parameters_queries pq7
, parameters_queries pq8
WHERE pq1.tag::text = 'COORDSYS'::text
AND pq2.tag::text = 'ROUTES'::text
AND pq2.legenda::text = 'PERIOD'::text
AND pq3.tag::text = 'ROUTES'::text
AND pq3.legenda::text = 'WORKSTATIONTIME'::text
AND pq4.tag::text = 'ROUTES'::text
AND pq4.legenda::text = 'WEEK'::text
AND pq5.tag::text = 'ROUTES'::text
AND pq5.legenda::text = 'RANK'::text
AND pq6.tag::text = 'ROUTES'::text
AND pq6.legenda::text = 'SPEED'::text
AND pq7.tag::text = 'ROUTES'::text
AND pq7.legenda::text = 'GPSTIME'::text
AND pq8.tag::text = 'ROUTES'::text
AND pq8.legenda::text = 'RANKTYPE'::text
) pq,
(
SELECT routes.rank
, routes.period_date
, routes.workstation_time
, routes.gps_time
, routes.week_date
, routes.survey_date
, routes.geo_point
, routes.lon
, routes.lat
, routes.quota
, routes.y
, routes.x
, routes.z
, routes.speed
, routes.direction
, routes.segment_utm_wgs84 AS segment
FROM routes
, rank_type
WHERE routes.rank_type = rank_type.rank_type
AND geo_point > 90000000
) rou
right outer join
(
SELECT pq1.legenda AS coordinates_system
, polygon
, cell_length
, cells_side
, grd.parameter AS parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, grd.polygon_utm_wgs84 AS cell
FROM delphis.grids grd
, delphis.parameters_grid pgr
, delphis.parameters_queries pq1
, delphis.parameters_queries pq2
, delphis.parameters_queries pq3
, delphis.parameters_queries pq4
WHERE pq1.tag::text = 'COORDSYS'::text
AND pq2.tag::text = 'GRID02'::text
AND pq2.legenda::text = 'CELL_LENGTH'::text
AND pq3.tag::text = 'GRID02'::text
AND pq3.legenda::text = 'LOW_LEFT_CORNER'::text
AND pq4.tag::text = 'GRID02'::text
AND pq4.legenda::text = 'HIGH_RIGHT_CORNER'::text
AND pgr.cell_length = pq2.min
AND grd.parameter = pgr.parameter
AND 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)
)
) grd
ON ST_INTERSECTS(segment,cell)
GROUP
BY grd.coordinates_system
, polygon
, cell_length
, cells_side
, parameter
, lon_index
, lat_index
, quota_index
, lon_south_west
, lat_south_west
, quota_south_west
, lon_south_east
, lat_south_east
, quota_south_east
, lon_north_east
, lat_north_east
, quota_north_east
, lon_north_west
, lat_north_west
, quota_north_west
, cell
ORDER
BY polygon;
So this is the explain plan:
"GroupAggregate (cost=14064.20..14065.30 rows=1 width=1054) (actual time=8239531.621..8247508.994 rows=8281 loops=1)"
" -> Sort (cost=14064.20..14064.21 rows=1 width=1054) (actual time=8239531.566..8239600.772 rows=30857 loops=1)"
" 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"
" Sort Method: external merge Disk: 10824kB"
" -> Nested Loop (cost=683.79..14064.19 rows=1 width=1054) (actual time=48511.602..8238741.620 rows=30857 loops=1)"
" -> Nested Loop (cost=0.00..15.59 rows=1 width=0) (actual time=0.254..0.346 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..13.78 rows=1 width=0) (actual time=0.234..0.312 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..11.81 rows=1 width=0) (actual time=0.209..0.273 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..9.84 rows=1 width=0) (actual time=0.193..0.239 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..7.87 rows=1 width=0) (actual time=0.152..0.193 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..5.90 rows=1 width=0) (actual time=0.127..0.154 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..3.93 rows=1 width=0) (actual time=0.086..0.106 rows=1 loops=1)"
" -> 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)"
" Filter: (((tag)::text = 'ROUTES'::text) AND ((legenda)::text = 'PERIOD'::text))"
" -> 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)"
" Filter: (((pq8.tag)::text = 'ROUTES'::text) AND ((pq8.legenda)::text = 'RANKTYPE'::text))"
" -> 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)"
" Filter: (((pq7.tag)::text = 'ROUTES'::text) AND ((pq7.legenda)::text = 'GPSTIME'::text))"
" -> 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)"
" Filter: (((pq6.tag)::text = 'ROUTES'::text) AND ((pq6.legenda)::text = 'SPEED'::text))"
" -> 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)"
" Filter: (((pq5.tag)::text = 'ROUTES'::text) AND ((pq5.legenda)::text = 'RANK'::text))"
" -> 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)"
" Filter: (((pq4.tag)::text = 'ROUTES'::text) AND ((pq4.legenda)::text = 'WEEK'::text))"
" -> 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)"
" Filter: (((pq3.tag)::text = 'ROUTES'::text) AND ((pq3.legenda)::text = 'WORKSTATIONTIME'::text))"
" -> 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)"
" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"
" -> Nested Loop (cost=683.79..14048.59 rows=1 width=1054) (actual time=48511.340..8238655.685 rows=30857 loops=1)"
" -> Nested Loop (cost=683.79..14046.78 rows=1 width=1044) (actual time=48511.320..8238030.935 rows=30857 loops=1)"
" Join Filter: (pq2.min = (pgr.cell_length)::double precision)"
" -> Nested Loop (cost=683.79..14046.50 rows=1 width=1044) (actual time=159.018..8235211.657 rows=184769 loops=1)"
" -> Nested Loop Left Join (cost=683.79..14044.53 rows=1 width=1036) (actual time=158.985..8229965.137 rows=184769 loops=1)"
" Join Filter: ((routes.segment_utm_wgs84 && grd.polygon_utm_wgs84) AND _st_intersects(routes.segment_utm_wgs84, grd.polygon_utm_wgs84))"
" -> Nested Loop (cost=0.02..12.33 rows=1 width=728) (actual time=0.359..849.907 rows=81964 loops=1)"
" -> Nested Loop (cost=0.00..3.93 rows=1 width=48) (actual time=0.071..0.093 rows=1 loops=1)"
" -> 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)"
" Filter: (((tag)::text = 'GRID02'::text) AND ((legenda)::text = 'LOW_LEFT_CORNER'::text))"
" -> 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)"
" Filter: (((pq4.tag)::text = 'GRID02'::text) AND ((pq4.legenda)::text = 'HIGH_RIGHT_CORNER'::text))"
" -> 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)"
" 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)"
" -> Hash Join (cost=683.78..9633.75 rows=16756 width=308) (actual time=2.583..77.226 rows=17274 loops=81964)"
" Hash Cond: (routes.rank_type = rank_type.rank_type)"
" -> Bitmap Heap Scan on routes (cost=682.15..9401.72 rows=16756 width=312) (actual time=2.578..25.357 rows=17274 loops=81964)"
" Recheck Cond: (geo_point > 90000000)"
" -> 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)"
" Index Cond: (geo_point > 90000000)"
" -> Hash (cost=1.28..1.28 rows=28 width=4) (actual time=0.119..0.119 rows=26 loops=1)"
" -> Seq Scan on rank_type (cost=0.00..1.28 rows=28 width=4) (actual time=0.029..0.067 rows=26 loops=1)"
" -> 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)"
" Filter: (((pq2.tag)::text = 'GRID02'::text) AND ((pq2.legenda)::text = 'CELL_LENGTH'::text))"
" -> 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)"
" Index Cond: (pgr.parameter = grd.parameter)"
" -> 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)"
" Filter: ((pq1.tag)::text = 'COORDSYS'::text)"
"Total runtime: 8247522.985 ms"
2 hours and twenty minutes to add a simple natural join in a subquery.....
Am i make a some stupid mistaken or is there some bugs ?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100315/2ece3af1/attachment.html>
More information about the postgis-users
mailing list