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

[Paul Ramsey]

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, <adam at spatialsystems.org> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>