pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steven Butler <stevenb@kjross.com.au>
To: Chris Smith <chris@interspire.com>
To: pgsql-performance@postgresql.org
Subject: Re: simple query join
Date: Mon, 8 Mar 2004 17:11:56 +1000
Message-ID: <006901c404dc$a43af520$fd08a8c0@steve> (raw)
References: <002201c404da$93d50790$0d00a8c0@chris>

MessageLooks 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)

view thread (4+ messages)  latest in thread

Message-ID: <006901c404dc$a43af520$fd08a8c0@steve>
Permalink:  ../006901c404dc$a43af520$fd08a8c0@steve/
Also on:    postgresql.org/message-id/006901c404dc$a43af520$fd08a8c0@steve

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stevenb@kjross.com.au, chris@interspire.com
  Subject: Re: simple query join
  In-Reply-To: <006901c404dc$a43af520$fd08a8c0@steve>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox