Re: simple query join - Mailing list pgsql-performance

From Steven Butler
Subject Re: simple query join
Date
Msg-id 006901c404dc$a43af520$fd08a8c0@steve
Whole thread Raw
In response to simple query join  ("Chris Smith" <chris@interspire.com>)
Responses Re: simple query join  ("Chris Smith" <chris@interspire.com>)
List pgsql-performance
Looks to me like it's because your assetid is varchar in one table and an integer in the other table.  AFAIK, PG is unable to use an index join when the join types are different.  The query plan shows it is doing full table scans of both tables.
 
Change both to varchar or both to integer and see what happens.
 
Also make sure to vacuum analyze the tables regularly to keep the query planner statistics up-to-date.
 
Cheers,
Steve Butler
 assetid        | integer                     | not null default 0
Indexes: sq_asset_pkey primary key btree (assetid)
 assetid    | character varying(255) | not null default '0'
EXPLAIN ANALYZE SELECT p.*
FROM sq_asset a, sq_asset_permission p
WHERE a.assetid = p.assetid
AND p.permission = '1'
AND p.access = '1'
AND p.userid = '0';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..4743553.10 rows=2582 width=27) (actual time=237.91..759310.60 rows=11393 loops=1)
   Join Filter: (("inner".assetid)::text = ("outer".assetid)::text)
   ->  Seq Scan on sq_asset_permission p  (cost=0.00..1852.01 rows=2288 width=23) (actual time=0.06..196.90 rows=12873 loops=1)
         Filter: ((permission = 1) AND ("access" = '1'::bpchar) AND (userid = '0'::character varying))
   ->  Seq Scan on sq_asset a  (cost=0.00..1825.67 rows=16467 width=4) (actual time=1.40..29.09 rows=16467 loops=12873)
 Total runtime: 759331.85 msec
(6 rows)

pgsql-performance by date:

Previous
From: "Chris Smith"
Date:
Subject: simple query join
Next
From: Dennis Bjorklund
Date:
Subject: Re: simple query join