Re: Query is stuck

OK, I added now index:

Create index repcopy_index on repcopy (dm_user, dm_ip)

And even then query is taking long time. See below. As I mentioned
before, for dm_user=u9 I have about 10,000 records and for dm_user=u9 I
have about 25000 records. As you see in the output below, for u9, I get
results in 8.7 ms but for u3 it is very huge 689111 ms. What else do you
think I can change to make results faster ?

controlsmartdb=# explain analyze select report_id, dm_ip, dm_mac,
dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain,
ss_key, login_time, role_id, new_vlan_id from repcopy as a where
report_time = (select max(report_time) from repcopy as b where
a.dm_user=b.dm_user and a.dm_ip = b.dm_ip and b.ss_key != '') and
report_status = 0 and dm_user = 'u3';

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------
Index Scan using repcopy_index on repcopy a (cost=0.00..87824607.17
rows=28 width=142) (actual time=11773.105..689111.440 rows=1 loops=1)
Index Cond: ((dm_user)::text = 'u3'::text)
Filter: ((report_status = 0) AND (report_time = (subplan)))
SubPlan
-> Aggregate (cost=3531.30..3531.31 rows=1 width=8) (actual
time=58.447..58.448 rows=1 loops=11788)
-> Index Scan using repcopy_index on repcopy b
(cost=0.00..3526.30 rows=2000 width=8) (actual time=0.017..36.779
rows=25842 loops=11788)
Index Cond: ((($0)::text = (dm_user)::text) AND
(($1)::text = (dm_ip)::text))
Filter: ((ss_key)::text <> ''::text)
Total runtime: 689111.511 ms
(9 rows)

controlsmartdb=# explain analyze select report_id, dm_ip, dm_mac,
dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain,
ss_key, login_time, role_id, new_vlan_id from repcopy as a where
report_time = (select max(report_time) from repcopy as b where
a.dm_user=b.dm_user and a.dm_ip = b.dm_ip and b.ss_key != '') and
report_status = 0 and dm_user = 'u9';
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------------
Index Scan using repcopy_index on repcopy a (cost=0.00..42856286.47
rows=14 width=142) (actual time=8.613..8.613 rows=0 loops=1)
Index Cond: ((dm_user)::text = 'u9'::text)
Filter: ((report_status = 0) AND (report_time = (subplan)))
SubPlan
-> Aggregate (cost=3531.30..3531.31 rows=1 width=8) (never
executed)
-> Index Scan using repcopy_index on repcopy b
(cost=0.00..3526.30 rows=2000 width=8) (never executed)
Index Cond: ((($0)::text = (dm_user)::text) AND
(($1)::text = (dm_ip)::text))
Filter: ((ss_key)::text <> ''::text)
Total runtime: 8.670 ms
(9 rows)

-----Original Message-----
From: Bill Moran [mailto:wmoran(at)potentialtech(dot)com]
Sent: Wednesday, April 14, 2010 6:06 PM
To: Satish Burnwal (sburnwal)
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Query is stuck

Unless you truncated this output, you _really_ need to add some indexes
to this table. Read back through earlier messages in the thread for
suggestions.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/