Title: Message
|
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.
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)
|
