Two things. First, if you have a lot of distinct channels, you'll find multikey btree on channel,date is better than date,channel. Second, your join key is SnapToGrid() which is to say you're joining on an unindexed value. Your best bet might be to pre-calculate the grid value into a column, if you have 1.4 you could use the geohash of the grid as the key, and then index that column. That will make your self-join much faster.
P. On Thu, Jul 9, 2009 at 2:17 PM, <[email protected]> wrote: > 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 > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
