Re: simple query join

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(at)kjross(dot)com(dot)au]
Sent: Monday, March 08, 2004 6:12 PM
To: Chris Smith; pgsql-performance(at)postgresql(dot)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)