From: Chris Smith <chris@interspire.com>
To: pgsql-performance@postgresql.org
Subject: Re: simple query join
Date: Tue, 9 Mar 2004 08:43:40 +1100
Message-ID: <000501c40556$6c25cf00$0d00a8c0@chris> (raw)
In-Reply-To: <006901c404dc$a43af520$fd08a8c0@steve>
Eek. Casting both to varchar makes it super quick so I'll fix up the
tables.
Added to the list of things to check for next time...
On a side note - I tried it with 7.4.1 on another box and it handled it
ok.
Thanks again :)
Chris.
-----Original Message-----
From: Steven Butler [mailto:stevenb@kjross.com.au]
Sent: Monday, March 08, 2004 6:12 PM
To: Chris Smith; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] simple query join
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)
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: chris@interspire.com
Subject: Re: simple query join
In-Reply-To: <000501c40556$6c25cf00$0d00a8c0@chris>
* 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