Re: Query is stuck - Mailing list pgsql-general

controlsmartdb=# \d repcopy;
                    Table "public.repcopy"
     Column      |              Type              | Modifiers
-----------------+--------------------------------+-----------
 report_id       | integer                        | not null
 dm_ip           | character varying(64)          |
 dm_mac          | character varying(64)          |
 dm_user         | character varying(255)         |
 dm_os           | character varying(64)          |
 report_time     | timestamp(0) without time zone |
 sys_name        | character varying(255)         |
 sys_user        | character varying(255)         |
 sys_user_domain | character varying(255)         |
 ss_key          | character varying(128)         |
 login_time      | character varying(64)          |
 role_id         | smallint                       |
 new_vlan_id     | character varying(64)          |
 report_status   | smallint                       |
Indexes:
    "repcopy_pk" PRIMARY KEY, btree (report_id)

controlsmartdb=# select count(*) from repcopy where dm_user = 'u3';
 count
-------
 25842
(1 row)

controlsmartdb=# select count(*) from repcopy where dm_user = 'u9';
 count
-------
 10283
(1 row)

As you see, for dm_user = 'u9', the original query :
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_ip =
b.dm_ipand a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = 'u1';
 

gives the result in less than a second. But when dm_user = 'u3' is used, it is taking very loooong time. Just 2.5 times
thenumber of records is increasing the query time by more than 1000 times.
 

Also, can you tell me whether in this case, I shall create index jointly on (dm_ip, dm_user) or separately on them ?

Thanks
-Satish


-----Original Message-----
From: tv@fuzzy.cz [mailto:tv@fuzzy.cz] 
Sent: Tuesday, April 13, 2010 7:56 PM
To: Satish Burnwal (sburnwal)
Cc: Bill Moran; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [ADMIN] Query is stuck


I'd recommend creating a index on (dm_user, dm_ip) columns, but it depends
on how many different values are in these columns (the more the better).

What information do we need to give better recommendations:

1) info about structure of the "repcopy" table (column data types, indexes)
2) info about data (how many different values are there)
3) what does the system do when running the query (use 'top' or 'dstat' to
get iowait / CPU / disk / memory etc.)

regards
Tomas