Interpreting query plan - Mailing list pgsql-general

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..133.81 rows=1 width=55)
   ->  Sort  (cost=133.78..133.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..133.77 rows=1 width=55)
               Hash Cond: ("outer".userid = "inner".userid)
               ->  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_lookup 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.useraccount"
    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_pkey" primary key, btree (userid)
    "useraccount_lookup" unique, btree (companyid, username)
Triggers:
    "RI_ConstraintTrigger_255906" AFTER INSERT OR UPDATE ON useraccount FROM
com
pany NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
check_ins"('useraccount_fk1', 'useraccount', 'company', 'UNSPECIFIED',
'companyi
d', 'companyid')
    "RI_ConstraintTrigger_255916" AFTER DELETE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_del"('registrationfield_fk2', 'registrationfield', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
    "RI_ConstraintTrigger_255917" AFTER UPDATE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_upd"('registrationfield_fk2', 'registrationfield', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
    "RI_ConstraintTrigger_255919" AFTER DELETE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_
del"('userrole_fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
    "RI_ConstraintTrigger_255920" AFTER UPDATE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_
upd"('userrole_fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
    "RI_ConstraintTrigger_255928" AFTER DELETE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del
"('visit_fk1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
    "RI_ConstraintTrigger_255929" AFTER UPDATE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd
"('visit_fk1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
    "RI_ConstraintTrigger_255940" AFTER DELETE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_del"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
    "RI_ConstraintTrigger_255941" AFTER UPDATE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_upd"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')


miqweb=> \d usermapping
  Table "public.usermapping"
 Column  |  Type   | Modifiers
---------+---------+-----------
 userid  | integer | not null
 groupid | integer | not null
Foreign-key constraints:
    "$1" FOREIGN KEY (userid) REFERENCES useraccount(userid)
    "$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