I've just noticed in the regular profiling information from our web
application that a particular query on a fairly small database is taking about
15 seconds. The query is generated from software on the fly, hence its
quirkiness -- if any of that is the problem, then I'll go ahead and fix it,
but I can't imagine a few repeated WHERE conditions fooling the query
optimizer.
Anyway, I don't know how to interpret query plans. Can anyone give me a hand?
To get the plan, I just plugged in various values -- The actual query is run
with various different values, and even a few different lengths for the IN
clause.
miqweb=> explain select distinct t0.* from UserAccount t0, UserMapping t1
where
(t0.companyid = 123) and ((t0.companyid = 123) and (t0.userid = t1.userid)
and
(t1.groupid in (123, 234, 345, 456))) and (t0.companyid = 123);
QUERY PLAN
------------------------------------------------------------------------------
--
--------------------------------------------------------------
Unique (cost=133.78..1 33.81 rows=1 width=55)
-> Sort (cost=133.78..1 33.79 rows=1 width=55)
Sort Key: t0.userid, t0.companyid, t0.username, t0."password",
t0.isact
ive, t0.isregistered , t0.lastlogin, t0.firstname, t0.lastname
-> Hash Join (cost=13.44..13 3.77 rows=1 width=55)
Hash Cond: ("outer".use rid = "inner".use rid)
-> Seq Scan on usermapping t1 (cost=0.00..120 .26 rows=13
width=
4)
Filter: ((groupid = 123) OR (groupid = 234) OR (groupid =
3
45) OR (groupid = 456))
-> Hash (cost=13.43..13 .43 rows=4 width=55)
-> Index Scan using useraccount_loo kup on useraccount t0
(cost=0.00..13. 43 rows=4 width=55)
Index Cond: (companyid = 123)
(10 rows)
And relevant tables (apparently a little messed up by prior database version
upgrades, so that come of the foreign keys show up directly as triggers):
miqweb=> \d useraccount
Table "public.useracc ount"
Column | Type | Modifiers
--------------+---------+-----------
userid | integer | not null
companyid | integer | not null
username | text | not null
password | text | not null
isactive | boolean | not null
isregistered | boolean | not null
lastlogin | date |
firstname | text |
lastname | text |
Indexes:
"useraccount_pk ey" primary key, btree (userid)
"useraccount_lo okup" unique, btree (companyid, username)
Triggers:
"RI_ConstraintT rigger_255906" AFTER INSERT OR UPDATE ON useraccount FROM
com
pany NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
check_ins"('use raccount_fk1', 'useraccount', 'company', 'UNSPECIFIED',
'companyi
d', 'companyid')
"RI_ConstraintT rigger_255916" AFTER DELETE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_del"(' registrationfie ld_fk2', 'registrationfi eld', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
"RI_ConstraintT rigger_255917" AFTER UPDATE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_upd"(' registrationfie ld_fk2', 'registrationfi eld', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
"RI_ConstraintT rigger_255919" AFTER DELETE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti on_
del"('userrole_ fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
"RI_ConstraintT rigger_255920" AFTER UPDATE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti on_
upd"('userrole_ fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
"RI_ConstraintT rigger_255928" AFTER DELETE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti on_del
"('visit_fk 1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
"RI_ConstraintT rigger_255929" AFTER UPDATE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti on_upd
"('visit_fk 1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
"RI_ConstraintT rigger_255940" AFTER DELETE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti o
n_del"('adminvi sit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
"RI_ConstraintT rigger_255941" AFTER UPDATE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noacti o
n_upd"('adminvi sit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
miqweb=> \d usermapping
Table "public.usermap ping"
Column | Type | Modifiers
---------+---------+-----------
userid | integer | not null
groupid | integer | not null
Foreign-key constraints:
"$1" FOREIGN KEY (userid) REFERENCES useraccount(use rid)
"$2" FOREIGN KEY (groupid) REFERENCES groups(groupid)
--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
