I've got a query like this:

SELECT count(*) 
FROM acs2014_5yr.geoheader as g 
JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) 
JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) 

and I'd like to add this filter to it:WHERE g.blkgrp IS NULL AND c.metro_id = '348'. However, the query (without the WHERE clause) takes about 1.3 seconds. When I add the WHERE clause, the query takes more than 3 minutes (too slow for production). This doesn't make any sense to me, since it's just a filter on rows already being iterated through in the query.

Also, if I take either half of the WHERE query out (so it's either WHERE g.blkgrp IS NULL or WHERE c.metro_id = '348') the query remains extremely performant (about 1.5 seconds). What's up with this behavior? I've been looking at this for more than a day and still don't have a clue as to what's killing performance here.

A couple notes:

  • There are no indices on either g.blkgrp or c.metro_id. This shouldn't matter though because there are only a couple thousand (< 4,000) rows in c, and each half of the WHERE clause is performant enough on its own.
  • Again, there are only a couple thousand rows in c, so even if this was causing an additional full table scan, performance shouldn't be hurting that much.
  • If you're having any trouble visualizing what I'm trying to do here, acs2014_5yr.geoheader and tiger2014.tract have a one-to-one relationship through tractcache.

Again, I can't figure this out for the life of me. I sincerely appreciate any help I get.

Output of EXPLAIN for the performant query (no WHERE clause at all):

Hash Join  (cost=68995.64..71160.78 rows=1643543 width=24)
  Hash Cond: ((c.tract_id)::text = (t.geoid)::text)
  ->  Seq Scan on tractcache c  (cost=0.00..42.68 rows=2068 width=56)
  ->  Hash  (cost=64990.76..64990.76 rows=158950 width=76)
        ->  Hash Join  (cost=24259.82..64990.76 rows=158950 width=76)
              Hash Cond: ((((g.state)::text || (g.county)::text) || (g.tract)::text) = (t.geoid)::text)
              ->  Seq Scan on geoheader g  (cost=0.00..33379.50 rows=158950 width=64)
              ->  Hash  (cost=23333.81..23333.81 rows=74081 width=12)
                    ->  Seq Scan on tract t  (cost=0.00..23333.81 rows=74081 width=12)

Output of EXPLAIN for the poorly performing query (with WHERE g.blkgrp IS NULL AND c.metro_id = '348'):

Nested Loop  (cost=47.98..57198.21 rows=40 width=24)
  Join Filter: ((c.tract_id)::text = (((g.state)::text || (g.county)::text) || (g.tract)::text))
  ->  Seq Scan on geoheader g  (cost=0.00..33379.50 rows=795 width=64)
        Filter: (blkgrp IS NULL)
  ->  Materialize  (cost=47.98..23659.74 rows=10 width=68)
        ->  Hash Join  (cost=47.98..23659.69 rows=10 width=68)
           Hash Cond: ((t.geoid)::text = (c.tract_id)::text)
           ->  Seq Scan on tract t  (cost=0.00..23333.81 rows=74081 width=12)
           ->  Hash  (cost=47.85..47.85 rows=10 width=56)
                 ->  Seq Scan on tractcache c  (cost=0.00..47.85 rows=10 width=56)
                       Filter: ((metro_id)::text = '348'::text)

UPDATE: Apparently this has to do with database configuration, it runs fine locally (in a docker container). The database is managed by RDS.

  • Show us table definitions, indexes included – ypercubeᵀᴹ May 13 '16 at 19:04
  • 1.3 seconds seems still to be inefficient. But we can't help without more details. All these concatenations in the joins conditions look bad. What are the types of the columns? What indexes do you have? etc – ypercubeᵀᴹ May 13 '16 at 20:36
  • Filter: ((metro_id)::text = '348'::text) - something wrong with types. – Abelisto May 14 '16 at 18:43
  • @ypercubeᵀᴹ No indices, but I wouldn't think that would matter on a filter. The types of all the columns are text (various length VARCHARs). Also interesting is that this query runs fine locally, and there is no Materialize block in the output of EXPLAIN. It's only in an AWS RDS provisioned database that it's slow. Here's the full output of EXPLAIN locally: gist.github.com/cderwin/e0a393f4f0d597f1afecff375bd5de9e – cderwin May 15 '16 at 20:11
  • @AbelistoWhat's wrong with the types here? They are actually both text types – cderwin May 15 '16 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.