Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal code. Here are the two queries that I need to do in order to get the data I need:

SELECT coord FROM public.postal_code WHERE postal_code = 'T1K0T4' LIMIT 1

query time: 6ms

This gives me the coordinate of the center of the postal_code area. Then I use this coordinate to find which timezone it intersects:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects(ST_GeomFromText('POINT(-112 49)',4326),geom)

query time: 36ms

Now when I combine the 2 queries, the query time jumps to 7-8 seconds. This is my query:

SELECT *
FROM public.timezones as tz
WHERE ST_Intersects((SELECT coord FROM taduler.postal_code
                     WHERE postal_code = 'T1K0T4' LIMIT 1),geom)

I have a spatial index on the coord column in the postal_code table, and also on the geom column in the timezone table, but it seems like it isn't being used for the subquery.

Does anyone know of a better way to optimize this query? I have tried several variations of this query, like joining the tables and such, but they have all resulted in the same query speed.

Output from EXPLAIN ANALYZE:

Seq Scan on timezones tz  (cost=8.37..167.47 rows=136 width=335547) (actual time=4606.136..7274.428 rows=1 loops=1)
  Filter: st_intersects($0, (geom)::geography)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..8.37 rows=1 width=128) (actual time=0.011..0.011 rows=1 loops=1)
          ->  Index Scan using postal_code_idx on postal_code  (cost=0.00..8.37 rows=1 width=128) (actual time=0.010..0.010 rows=1 loops=1)
                Index Cond: ((postal_code)::text = 'T1K0T4'::text)
Total runtime: 7274.448 ms

EXPLAIN ANALYZE for the following query:

SELECT *
FROM public.timezones as tz
JOIN taduler.postal_code as pc on ST_Intersects(pc.coord, tz.geom)
WHERE pc.postal_code = 'T1K0T4'

Output:

Nested Loop  (cost=0.00..174.61 rows=1 width=335714) (actual time=4870.908..7572.723 rows=1 loops=1)
  Join Filter: ((pc.coord && (tz.geom)::geography) AND (_st_distance(pc.coord, (tz.geom)::geography, 0::double precision, false) < 1e-05::double precision))
  ->  Index Scan using postal_code_idx on postal_code pc  (cost=0.00..8.37 rows=1 width=167) (actual time=0.012..0.019 rows=1 loops=1)
        Index Cond: ((postal_code)::text = 'T1K0T4'::text)
  ->  Seq Scan on timezones tz  (cost=0.00..56.08 rows=408 width=335547) (actual time=0.002..2.795 rows=408 loops=1)
Total runtime: 7572.787 ms
share|improve this question

migrated from stackoverflow.com Mar 24 at 22:42

This question came from our site for professional and enthusiast programmers.

    
please post the query plan (EXPLAIN ANALYZE) –  swasheck Mar 24 at 21:10
    
What is the coord for that postal_code? What type and coordinate system? Because it doesn't think it can use the index. So compare the two. –  Richard Huxton Mar 24 at 21:19
    
"POINT(-112.823003 49.697541)" is the coord, using EPSG 4326 –  Sehael Mar 24 at 21:21
    
@Richard Huxton, I also realized the postal_code is geography and the timezone is using geometry. That may be the issue, but I'm no expert with PostGIS. –  Sehael Mar 24 at 21:22
    
@RichardHuxton thanks to your questions, I was able to figure out that the problem was the data types of the two columns. They both need to be the same data type, so once I changed the geometry column to geography, the query runs as expected. If you post that as an answer, I will accept it. –  Sehael Mar 24 at 21:41

1 Answer 1

Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here:
Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops")

In Postgres 9.3 You could try this version with LEFT JOIN LATERAL:

SELECT *
FROM  (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    ) pc
LEFT JOIN LATERAL (
    SELECT *
    FROM   public.timezones tz
    WHERE  ST_Intersects(pc.coord, tz.geom)
   ) tz ON TRUE;

Something similar Worked for @ypercube's solution in this related answer.
LATERAL requires Postgres 9.3+, though.

In PostgreSQL 9.1, it might help to encapsulate the first query in a CTE, but I doubt it. (Don't have a PostGis installation here to test.):

WITH pc AS (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    )
SELECT *
FROM   pc
JOIN   public.timezones tz ON ST_Intersects(pc.coord, tz.geom);

A plpgsql function to encapsulate two separate queries should certainly do the trick:

CREATE OR REPLACE FUNCTION f_get_tz(_pc text)
  RETURNS SETOF public.timezones AS
$func$
DECLARE
   _coord geom;
BEGIN

SELECT coord
INTO  _coord
FROM   taduler.postal_code
WHERE  postal_code = _pc
LIMIT  1;

RETURN QUERY
SELECT *
FROM   public.timezones tz
WHERE  ST_Intersects(_coord, tz.geom);

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_get_tz('T1K0T4');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.