simple query join - Mailing list pgsql-performance
| From | Chris Smith |
|---|---|
| Subject | simple query join |
| Date | |
| Msg-id | 002201c404da$93d50790$0d00a8c0@chris Whole thread Raw |
| Responses |
Re: simple query join
|
| List | pgsql-performance |
Hi all,
I've got what should be a relatively simple join between two tables that is taking forever and I can't work out why.
Version 7.3.4RH.
It can't be upgraded because the system is kept in sync with RedHat Enterprise (using up2date). Not my system otherwise I'd do that :(
Database has been 'vacuum analyze'd.
blah=> \d sq_asset;
Table "public.sq_asset"
Column | Type | Modifiers
----------------+-----------------------------+--------------------------
type_code | character varying(100) | not null
version | character varying(20) | not null default '0.0.0'
name | character varying(255) | not null default ''
short_name | character varying(255) | not null default ''
status | integer | not null default 1
languages | character varying(50) | not null default ''
charset | character varying(50) | not null default ''
force_secure | character(1) | not null default '0'
created | timestamp without time zone | not null
updated | timestamp without time zone | not null
created_userid | character varying(255) | not null default '0'
updated_userid | character varying(255) | not null default '0'
assetid | integer | not null default 0
Indexes: sq_asset_pkey primary key btree (assetid)
Table "public.sq_asset"
Column | Type | Modifiers
----------------+-----------------------------+--------------------------
type_code | character varying(100) | not null
version | character varying(20) | not null default '0.0.0'
name | character varying(255) | not null default ''
short_name | character varying(255) | not null default ''
status | integer | not null default 1
languages | character varying(50) | not null default ''
charset | character varying(50) | not null default ''
force_secure | character(1) | not null default '0'
created | timestamp without time zone | not null
updated | timestamp without time zone | not null
created_userid | character varying(255) | not null default '0'
updated_userid | character varying(255) | not null default '0'
assetid | integer | not null default 0
Indexes: sq_asset_pkey primary key btree (assetid)
blah=> select count(*) from sq_asset;
count
-------
16467
(1 row)
count
-------
16467
(1 row)
blah=> \d sq_asset_permission;
Table "public.sq_asset_permission"
Column | Type | Modifiers
------------+------------------------+----------------------
permission | integer | not null default 0
access | character(1) | not null default '0'
assetid | character varying(255) | not null default '0'
userid | character varying(255) | not null default '0'
Indexes: sq_asset_permission_pkey primary key btree (assetid, userid, permission)
"sq_asset_permission_access" btree ("access")
"sq_asset_permission_assetid" btree (assetid)
"sq_asset_permission_permission" btree (permission)
"sq_asset_permission_userid" btree (userid)
Table "public.sq_asset_permission"
Column | Type | Modifiers
------------+------------------------+----------------------
permission | integer | not null default 0
access | character(1) | not null default '0'
assetid | character varying(255) | not null default '0'
userid | character varying(255) | not null default '0'
Indexes: sq_asset_permission_pkey primary key btree (assetid, userid, permission)
"sq_asset_permission_access" btree ("access")
"sq_asset_permission_assetid" btree (assetid)
"sq_asset_permission_permission" btree (permission)
"sq_asset_permission_userid" btree (userid)
blah=> select count(*) from sq_asset_permission;
count
-------
73715
(1 row)
count
-------
73715
(1 row)
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)
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)
It's a straight join so I can't see why it would be this slow.. The tables are pretty small too.
Thanks for any suggestions :)
Chris.
pgsql-performance by date: