[GENERAL] Query to find sum of grouped counts from 2 tables
I have 2 tables containing the data for same items: STORE1 - Id typeitems - 1 FOOD10 2 FOOD15 3 SOAP20 STORE2 - Id typeitems - 1 FOOD15 3 SOAP10 4 PAPER 25 5 SOAP12 What I am looking for is one single query that would return me TYPE-wise total number of items from both the tables. UNION does not help me. I want the result as: Typecount --- FOOD40 //10+15+15 SOAP42 //20+10+12 PAPER 25 Thanks in advance, -Satish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need Help in query
I need a help in writing a query. I have data as follows: mydb=# select * from usrlog ; logid | userid | loginhr | logouthr ---+--+-+-- 0 | sburnwal | 0 |1 1 | rickyrs | 1 |5 2 | satishbn | 1 |6 3 | taohujin | 2 |4 4 | jospehm | 4 |5 Table captures the login and logout time (taking hour here to simplify) of users and my aim to find the number of logged-in users (online users) at every hr (1st hr i.e. 0-1, 2nd hrs i.e. 1-2, 3rd hr i.e. 2-3 and ... so on). As the data indicates, use is not logging out in same hr as hr of login. A user can be logged-in for more than one hr. For example, here user rickyrs is logged-in for 1st, 2nd, 3rd, 4th and 5th hr. My query needs to find out in the last 24 hrs, how many users were logged-in at each hr. I want the result this way: Nth-hr user --- 1 sburnwal 2 rickyrs 2 satishbn 3 rickyrs 3 satishbn 3 taohujin 4 rickyrs 4 satishbn 4 taohujin 4 josephm Appreciate your response in advance. For me, even the count of users on hourly basis is fine. Thanks -Satish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help in query
Thanks! I did not know such a function exists. From: Nicklas Avén [mailto:nicklas.a...@jordogskog.no] Sent: Thursday, December 23, 2010 3:31 PM To: Satish Burnwal (sburnwal) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need Help in query Hallo This I think should work. To get the usernames by hour as you describe: SELECT h.hour, usrlog.userid (select generate_series(1,24) as hour) as h inner join usrlog on h.hour = usrlog.loginhr and h.hour = usrlog.logouthr order by h.hour, usrlog.userid; To get the number of users per hour : Select h.hour, count(*) asNumberOfUsers (select generate_series(1,24) as hour) h inner join usrlog on h.hour = usrlog.loginhr and h.hour = usrlog.logouthr group by h.hour; HTH Nicklas 2010-12-23 skrev Satish Burnwal (sburnwal) : I need a help in writing a query. I have data as follows: mydb=# select * from usrlog ; logid | userid | loginhr | logouthr ---+--+-+-- 0 | sburnwal | 0 | 1 1 | rickyrs | 1 | 5 2 | satishbn | 1 | 6 3 | taohujin | 2 | 4 4 | jospehm | 4 | 5 Table captures the login and logout time (taking hour here to simplify) of users and my aim to find the number of logged-in users (online users) at every hr (1st hr i.e. 0-1, 2nd hrs i.e. 1-2, 3rd hr i.e. 2-3 and ... so on). As the data indicates, use is not logging out in same hr as hr of login. A user can be logged-in for more than one hr. For example, here user rickyrs is logged-in for 1st, 2nd, 3rd, 4th and 5th hr. My query needs to find out in the last 24 hrs, how many users were logged-in at each hr. I want the result this way: Nth-hr user --- 1 sburnwal 2 rickyrs 2 satishbn 3 rickyrs 3 satishbn 3 taohujin 4 rickyrs 4 satishbn 4 taohujin 4 josephm Appreciate your response in advance. For me, even the count of users on hourly basis is fine. Thanks -Satish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Complete row is fetched ?
I have a ques - say I have a table that has 10 columns. But in a simple select query from that table, I use just 3 columns. I want to know whether even for fetching 3 columns, read happens for all the 10 columns and out of that the required 3 columns are returned ? ie Does the complete row with all the 10 columns are fetched even though I need just 3 columns ? OR only 3 columns are fetched ? Satish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query is stuck
Great!! Your help is very valuable!! -Original Message- From: Justin Graf [mailto:jus...@magwerks.com] Sent: Wednesday, April 14, 2010 7:35 PM To: Bill Moran Cc: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Subject: Re: [GENERAL] Query is stuck I suggest writting something like this. 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 (select max(report_time) as rtime, dm_user, dm_ip from repcopy group by dm_user, dm_ip where ss_key != '') as materialized where report_time = materialized.rtime and materialized.dm_user = a.dm_user and materialized.dm__ip = a_ip and report_status = 0 and dm_user = 'u3'; All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query is stuck
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_ip and 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 lng time. Just 2.5 times the number 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: t...@fuzzy.cz [mailto:t...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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:wmo...@potentialtech.com] Sent: Wednesday, April 14, 2010 6:06 PM To: Satish Burnwal (sburnwal) Cc: pgsql-general@postgresql.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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query is stuck
I have a query which is not giving me the result even after 30 minutes. I want to know how to detect what is going and what's wrong ? EXPLAIN query - gives me the following: controlsmartdb=# explain 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_ip and a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = 'u1'; QUERY PLAN Seq Scan on repcopy a (cost=0.00..1630178118.35 rows=35 width=133) Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND (report_time = (subplan))) SubPlan - Aggregate (cost=8151.65..8151.66 rows=1 width=8) - Seq Scan on repcopy b (cost=0.00..8151.65 rows=1 width=8) Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = (dm_user)::text) AND ((ss_key)::text ''::text)) (6 rows) But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 minutes). Pg_stat_activity shows this - SELECT procpid, usename, current_query, query_start from pg_stat_activity: 2942 | postgres | 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_ip and a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30 In such a case what can I do ?
Re: [GENERAL] Query is stuck
controlsmartdb=# select * from pg_stat_activity where waiting='t'; ERROR: column waiting does not exist From: Plugge, Joe R. [mailto:jrplu...@west.com] Sent: Tuesday, April 13, 2010 6:32 PM To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject: RE: Query is stuck What do you get when you run this? select * from pg_stat_activity where waiting='t'; From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Satish Burnwal (sburnwal) Sent: Tuesday, April 13, 2010 7:58 AM To: pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject: [ADMIN] Query is stuck I have a query which is not giving me the result even after 30 minutes. I want to know how to detect what is going and what's wrong ? EXPLAIN query - gives me the following: controlsmartdb=# explain 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_ip and a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = 'u1'; QUERY PLAN Seq Scan on repcopy a (cost=0.00..1630178118.35 rows=35 width=133) Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND (report_time = (subplan))) SubPlan - Aggregate (cost=8151.65..8151.66 rows=1 width=8) - Seq Scan on repcopy b (cost=0.00..8151.65 rows=1 width=8) Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = (dm_user)::text) AND ((ss_key)::text ''::text)) (6 rows) But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 minutes). Pg_stat_activity shows this - SELECT procpid, usename, current_query, query_start from pg_stat_activity: 2942 | postgres | 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_ip and a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30 In such a case what can I do ?
Re: [GENERAL] Query is stuck
I am on postgres 8.1. bash-3.2$ postgres --version postgres (PostgreSQL) 8.1.11 From: Plugge, Joe R. [mailto:jrplu...@west.com] Sent: Tuesday, April 13, 2010 6:37 PM To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject: RE: Query is stuck What version of postgres are you on?
Re: [GENERAL] [ADMIN] Query is stuck
I am using 8.1, so waiting coln is not there in pg_stat_activity. I frequently see these in the server logs: LOG: autovacuum: processing database controlsmartdb Though I can give you the result of vacuum run (but it is not helping): controlsmartdb=# vacuum full verbose analyze repcopy; INFO: vacuuming public.repcopy INFO: repcopy: found 0 removable, 20 nonremovable row versions in 4652 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 182 to 182 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 416144 bytes. 0 pages are or will become empty, including 0 at the end of the table. 1 pages containing 6856 free bytes are potential move destinations. CPU 0.00s/0.02u sec elapsed 0.02 sec. INFO: index repcopy_pk now contains 20 row versions in 441 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.05s/0.00u sec elapsed 0.06 sec. INFO: repcopy: moved 0 row versions, truncated 4652 to 4652 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming pg_toast.pg_toast_18398 INFO: pg_toast_18398: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index pg_toast_18398_index now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing public.repcopy INFO: repcopy: scanned 3000 of 4652 pages, containing 128964 live rows and 0 dead rows; 3000 rows in sample, 199980 estimated total rows VACUUM controlsmartdb=# select distinct report_status from repcopy ; There is no update happening to the table. - 1. Check if the query waits on some lock: add the column `waiting` to the above query from pg_stat_activity. 2. Run vacuum analyze on the table repcopy In addition to that, indexes on report_time, report_status, and dm_user might help. And your query is not hung, it's just taking a LONG time. Based on the explain, it could take several hours to complete. How many rows are in repcopy? What is your vacuum schedule? Do a vacuum verbose, if the number of dead rows is very high on that table, you may benefit from doing a VACUUM FULL + REINDEX or CLUSTER on the table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Converting bytea to LargeObject
I am also having the problem as mentioned in http://archives.postgresql.org/pgsql-general/2009-01/msg00771.php. However, the sql statement: UPDATE tbl SET newoid = ( SELECT oid FROM ( SELECT oid, lowrite(lo_open(oid, 131072), byteafield) FROM lo_create(0) o(oid)) x); This is not working for me: - it is putting same newoid for all the records in the table whereas what is needed is separate oid for each record. Lo_create(0) seems to be taken for all the records. - can you pls explain the syntax o(oid) ? I do not see any documentation for this. Thanks Satish
