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@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, April 14, 2010 6:06 PM
To: Satish Burnwal (sburnwal)
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
