[postgis-users] Joining two GRID tables, slow query.

[adam at spatialsystems.org]

I'm have a table with 12,604,700 radio frequency measurements in a
PostGIS database.  Each measurement has with it the date it was taken. 
I want to compare bin-by-bin how the signal has changed before and after
2009/02/01.

The query below is taking a long time and I want to make sure I'm doing
it right and there isn't a faster way to do it.


Does my SQL have a fundamental flaw or can it be made faster?


On the table called "scanner" I have index on:

'channel' type btree
'date,channel' type btree
'geometry' type gist

SELECT
  X(pre_drive_test_data.grid) AS pre_x,
  Y(pre_drive_test_data.grid) AS pre_y,
  max(pre_drive_test_data.peak_rx::double precision ) as
pre_peak_rx_power,
  count(pre_drive_test_data.peak_rx) AS pre_count,
  X(post_drive_test_data.grid) AS post_x,
  Y(post_drive_test_data.grid) AS post_y,
  max(post_drive_test_data.peak_rx::double precision ) as
post_peak_rx_power,
  count(post_drive_test_data.peak_rx) AS post_count
FROM
(           
  SELECT
    SnapToGrid(scanner.geometry, 0.001213) AS grid,
    scanner.peak_ec as peak_ec
  FROM
    scanner
  WHERE
    scanner.channel = 700
    AND date < '2009-02-01'
) AS pre_drive_test_data,
(           
  SELECT
    SnapToGrid(scanner.geometry, 0.001213) AS grid,
    scanner.peak_ec as peak_ec
  FROM
    scanner
  WHERE
    scanner.channel = 700
    AND date > '2009-02-01'
) AS post_drive_test_data
WHERE 
  pre_drive_test_data.grid = post_drive_test_data.grid
GROUP BY
  pre_x,
  pre_y,
  post_x,
  post_y

 
Thanks,
Adam