Re: Interpreting query plan - Mailing list pgsql-general

Stephan,

Thanks for your reply.  Here is the output of "explain analyze".  I also
replaced by simple values with a real query that gets run and (according to
our profiling) takes a long time.  However, the query is now taking a much
shorter period of time than it was profiled at by the application.  I can only
guess the difference has something to do with system load.  I could try to run
this again during a high-load period, if that is necessary.  Let me know.

miqweb=> explain analyze select distinct t0.* from UserAccount t0, UserMapping
t1 where (t0.companyid = 628) and ((t0.companyid = 628) and (t0.userid =
t1.use
rid) and (t1.groupid in
(628,948,949,950,951,953,954,1272,1279,1296,1299,1300,1
363,1423,1446,1467,1526,1724,1735,1759,1763,1772,1785,1841,1862,1975,2721,2800
,
2801,2802,2803,1264,1394,1525,1662,1843,1844,1845,1396,1528,1860,1846,1762,242
2
,1271,1847,1848,1281,1849,1850,1851,1266,1809,1852,1853,2421,1854,1855,1913,18
5
6,1857,1269,1268,1858,1859,2804))) and (t0.companyid = 628);

[...]

 Unique  (cost=952.15..959.37 rows=289 width=55) (actual time=137.130..143.363
r
ows=752 loops=1)
   ->  Sort  (cost=952.15..952.87 rows=289 width=55) (actual
time=137.123..138.0
04 rows=1328 loops=1)
         Sort Key: t0.userid, t0.companyid, t0.username, t0."password",
t0.isact
ive, t0.isregistered, t0.lastlogin, t0.firstname, t0.lastname
         ->  Hash Join  (cost=869.15..940.34 rows=289 width=55) (actual
time=112
.112..130.948 rows=1328 loops=1)
               Hash Cond: ("outer".userid = "inner".userid)
               ->  Seq Scan on useraccount t0  (cost=0.00..55.71 rows=629
width=
55) (actual time=0.239..8.501 rows=753 loops=1)
                     Filter: (companyid = 628)
               ->  Hash  (cost=866.28..866.28 rows=1151 width=4) (actual
time=11
1.762..111.762 rows=0 loops=1)
                     ->  Seq Scan on usermapping t1  (cost=0.00..866.28
rows=115
1 width=4) (actual time=4.251..109.563 rows=1328 loops=1)
                           Filter: ((groupid = 628) OR (groupid = 948) OR
(group
id = 949) OR (groupid = 950) OR (groupid = 951) OR (groupid = 953) OR (groupid
=
 954) OR (groupid = 1272) OR (groupid = 1279) OR (groupid = 1296) OR (groupid
=
1299) OR (groupid = 1300) OR (groupid = 1363) OR (groupid = 1423) OR (groupid
=
1446) OR (groupid = 1467) OR (groupid = 1526) OR (groupid = 1724) OR (groupid
=
1735) OR (groupid = 1759) OR (groupid = 1763) OR (groupid = 1772) OR (groupid
=
1785) OR (groupid = 1841) OR (groupid = 1862) OR (groupid = 1975) OR (groupid
=
2721) OR (groupid = 2800) OR (groupid = 2801) OR (groupid = 2802) OR (groupid
=
2803) OR (groupid = 1264) OR (groupid = 1394) OR (groupid = 1525) OR (groupid
=
1662) OR (groupid = 1843) OR (groupid = 1844) OR (groupid = 1845) OR (groupid
=
1396) OR (groupid = 1528) OR (groupid = 1860) OR (groupid = 1846) OR (groupid
=
1762) OR (groupid = 2422) OR (groupid = 1271) OR (groupid = 1847) OR (groupid
=
1848) OR (groupid = 1281) OR (groupid = 1849) OR (groupid = 1850) OR (groupid
=
1851) OR (groupid = 1266) OR (groupid = 1809) OR (groupid = 1852) OR (groupid
=
1853) OR (groupid = 2421) OR (groupid = 1854) OR (groupid = 1855) OR (groupid
=
1913) OR (groupid = 1856) OR (groupid = 1857) OR (groupid = 1269) OR (groupid
=
1268) OR (groupid = 1858) OR (groupid = 1859) OR (groupid = 2804))
 Total runtime: 144.690 ms
(11 rows)


--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation