I only have two channel numbers and way more dates than channels. So I'm guessing my index is fine.
I'm running PostGIS 1.3.3; so I still can't use the geohash as the key? I figure I'd create a new column called "grid_geohash" as character_varying(255) and store the grid value there. Is this not possible in 1.3.3 or am I missing something? Thanks, Adam >-------- Original Message -------- >Subject: Re: [postgis-users] Joining two GRID tables, slow query. >From: Paul Ramsey <[email protected]> >Date: Thu, July 09, 2009 4:24 pm >To: PostGIS Users Discussion <[email protected]> > >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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
