Re: [SQL] subquery question

2009-03-12 Thread Bob Henkel
Does this help
Here is my test table data.
ID;DATE;VALUE
1;2009-03-13;5
2;2009-03-13;2
3;2009-03-11;1
4;2009-03-11;2
5;2009-03-11;3

SELECT mydate AS day,
   SUM(CASE WHEN id % 2 = 1 THEN value END) AS sum_odd,
   SUM(CASE WHEN id % 2 = 0 THEN value END) AS sum_even
FROM xyz
GROUP BY mydate;
DATE;SUM_ODD;SUM_EVEN
2009-03-11;4;2
2009-03-13;5;2

Check the plans generated to see if one query actually appears better
than another.

Bob


On Thu, Mar 12, 2009 at 9:06 PM, Sebastian Böhm s...@exse.net wrote:
 Hi,
 I have a table: (date timestamp, id integer, value integer)
 What Iam trying to do is to get a result that looks like this:
 day          sum_odd    sum_even
 2009-01-01     6565        78867
 2009-01-02     876785      87667

 basically a need to combine these two queries into one:
 SELECT    date_trunc('day',date) AS day,   sum(value) AS sum_odd     FROM
 xyz WHERE    id % 2 = 1    GROUP BY date_trunc('day',date)
 SELECT    date_trunc('day',date) AS day,   sum(value) AS sum_even    FROM
 xyz WHERE    id % 2 = 0    GROUP BY date_trunc('day',date)
 I found various ways to do this via unions or joins, but none of them seem
 efficient, what is the best way to do that ?

 thank you very much
 Sebastian

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
I might be missing something but does this solve your issue?

CREATE TABLE t1(d INT,s INT, c INT);

CREATE UNIQUE INDEX idx01_t1
ON t1 USING btree (d, s, c);

INSERT INTO t1 (d, s, c)
VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4);

CREATE TABLE t2(d INT,s INT, c INT, x boolean);

INSERT INTO t2(d, s, c, x)
VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE);

SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*)
FROM t1
INNER JOIN t2
ON t1.d = t2.d
   AND t1.s = t2.s
   AND t1.c = t2.c
   WHERE t2.x = 'FALSE'
GROUP BY t1.d, t1.s, t1.c, t2.x;

On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz
postgre...@numerixtechnology.de wrote:
 I have 2 tables T1 and T2

 T1 has the columns: D, S, C. The combination of D,S,C is unique.
 T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is
 not unique.

 I need to produce the following result for every occurrence of T1:
 D,S,C, COUNT

 COUNT is the number of matching D,S,C combinations in T2 where X = true.
 There might be no matching pair in T2 or there might be match but X
 is false.

 How can I express this?



 --


 Best Regards,

 Tarlika Elisabeth Schmitz

 --
 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
Scratch this one won't work for you.

On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel bob.hen...@gmail.com wrote:
 I might be missing something but does this solve your issue?

 CREATE TABLE t1(d INT,s INT, c INT);

 CREATE UNIQUE INDEX idx01_t1
 ON t1 USING btree (d, s, c);

 INSERT INTO t1 (d, s, c)
 VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4);

 CREATE TABLE t2(d INT,s INT, c INT, x boolean);

 INSERT INTO t2(d, s, c, x)
 VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE);

 SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*)
 FROM t1
 INNER JOIN t2
ON t1.d = t2.d
   AND t1.s = t2.s
   AND t1.c = t2.c
   WHERE t2.x = 'FALSE'
 GROUP BY t1.d, t1.s, t1.c, t2.x;

 On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz
 postgre...@numerixtechnology.de wrote:
 I have 2 tables T1 and T2

 T1 has the columns: D, S, C. The combination of D,S,C is unique.
 T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is
 not unique.

 I need to produce the following result for every occurrence of T1:
 D,S,C, COUNT

 COUNT is the number of matching D,S,C combinations in T2 where X = true.
 There might be no matching pair in T2 or there might be match but X
 is false.

 How can I express this?



 --


 Best Regards,

 Tarlika Elisabeth Schmitz

 --
 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-sql



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
How about this?

CREATE TABLE t1(d INT,s INT, c INT);

CREATE UNIQUE INDEX idx01_t1
ON t1 USING btree (d, s, c);

INSERT INTO t1 (d, s, c)
VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

CREATE TABLE t2(d INT,s INT, c INT, x boolean);

INSERT INTO t2(d, s, c, x)
VALUES 
(1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE),(5,5,5,TRUE),(5,5,5,TRUE);

SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) END
FROM t1
LEFT OUTER JOIN t2
ON t1.d = t2.d
   AND t1.s = t2.s
   AND t1.c = t2.c
   AND t2.x = TRUE
GROUP BY t1.d, t1.s, t1.c,t2.x;

--DROP TABLE t1;
--DROP TABLE t2;

On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz
postgre...@numerixtechnology.de wrote:
 I have 2 tables T1 and T2

 T1 has the columns: D, S, C. The combination of D,S,C is unique.
 T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is
 not unique.

 I need to produce the following result for every occurrence of T1:
 D,S,C, COUNT

 COUNT is the number of matching D,S,C combinations in T2 where X = true.
 There might be no matching pair in T2 or there might be match but X
 is false.

 How can I express this?



 --


 Best Regards,

 Tarlika Elisabeth Schmitz

 --
 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] getting count for a specific querry

2005-04-09 Thread Bob Henkel
On Apr 9, 2005 10:00 AM, John DeSoi [EMAIL PROTECTED] wrote:On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: I don't think my clients would like me to aprox as it is a count of their records. What I plan on doing assuming I can get all my other problems fixed (as mentioned I am going to try and get paid help to see if I goofed it up some where) is make the count a button, so they don't wait everytime, but can choose to wait if need be, maybe I can store the last count with a count on day for the generic search it defaults to, and just have them do a count on demand if they have a specific query. Our screens have several criteria fields in each application.Here is an interface idea I'm working on for displaying query resultsin PostgreSQL. Maybe it will work for you if your connection methoddoes not prevent you from using cursors. I create a cursor an thenfetch the first 1000 rows. The status display has 4 paging buttons,something like this:|  rows 1 - 1000 of ?  |The user can hit the next button to get the next 1000. If less than1000 are fetched the ? is replaced with the actual count. They canpress the last button to move to the end of the cursor and get theactual count if they need it. So here the initial query should be fast,the user can get the count if they need it, and you don't have tore-query using limit and offset.The problem I'm looking into now (which I just posted on the generallist) is I don't see a way to get the table and column information froma cursor. If I fetch from a cursor, the table OID and column numbervalues are 0 in the row description. If I execute the same querydirectly without a cursor, the row description has the correct valuesfor table OID and column number.John DeSoi, Ph.D.http://pgedit.com/Power Tools for PostgreSQL

Oracle Forms uses a similar method as you described and it works just
fine. It will say Record 1 of ?(But I think the developer can set
the amount of records cached so that if you set it to 10 and queried 5
records it would say record 1 of 5 because it would be under the cache
amount.) Forms also offers a
button that say get hit count. So if you really need to know the record
count you can get it without moving off the current record.

Re: [SQL] getting count for a specific querry

2005-04-08 Thread Bob Henkel
On Apr 8, 2005 1:10 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote: Thanks all. I might have to add a button to do the count on command so they don't get the hit. I would want it to return the count of the condition, not the currently displayed number of rows. Is there any other database engines that provide better performance? (We just 2 moths moving to postgres and it is not live yet, but if I am going to get results back slower then my 2 proc box running MSSQL in 2 gig and 2 processor I cant see any reason to move to it) The Postgres is on a 4 proc Dell with 8 gigs of memory. I thought I could analyze our queries and our config to optimize.Judging postgresql on one single data point (count(*) performance) isquite unfair.Unless your system only operates on static data and isused to mostly do things like counting, in which case, why are you usinga database?PostgreSQL is a great fit for certain loads, and a poor fit for others.Are you going to have lots of people updating the database WHILE theselect count(*) queries are running?Are you going to be doing other,more interesting things than simply counting?If so, you really shouldbuild a test case that emulates what you're really going to be doingwith the system.I've found that the poor performance of aggregates in PostgreSQL isgenerally more than made up for by the outstanding behaviour it exhibitswhen under heavy parallel load.Note that the basic design of PostgreSQL's MVCC system is such thatwithout using some kind of trigger to maintain pre-calculated aggregateinformation, it will NEVER be as fast as most other databases at doingaggregates across large chunks of your data.---(end of broadcast)---TIP 7: don't forget to increase your free space map settingsFrom a simple/high level perspective why is this? That is why can't PostgreSQL do aggregates as well across large chunks of data. I'm assuming it extremelycomplicated. Otherwise the folks around here would have churned out a fix in a month or less and made this issue a past story.

Re: [SQL] getting count for a specific querry

2005-04-08 Thread Bob Henkel
On Apr 8, 2005 2:23 PM, Joel Fradkin [EMAIL PROTECTED] wrote:
Believe me I just spent two months converting our app, I do not wish to giveup on that work. We do a great deal more then count. Specifically many ofour queries run much slower on postgres. As mentioned I purchased a 4 procbox with 8 gigs of memory for this upgrade (Dell may have been a poor choicebased on comments I have received). Even when I could see a query likeselect * from tblassoc where clientnum = 'WAZ' using indexed joins onlocation and jobtitle it is still taking 22 seconds to run compared to the 9seconds on MSSQL on a 2 proc 2 gig box. I got one of my querries to runfaster using a page cost of .2 but then the assoc query was running 50seconds, so I adjusted to a cost of 2 (tried 1.2, 2, 3, and 4 and did notsee hug changes in the assoc except it did not like .2).I have placed a call to commandprompt.com and am going to pay for somesupport to see if they have anything meaningful to add.It could be something with my hardware, my hardware config, my postgresconfig. I am just not sure. I know I have worked diligently to try to learnall I can and I used to think I was kinda smart.I set up the data on 4 10k scsi drives in a powervault and my wal on 2 15kdrives. I am using links to those from the install directory. It starts andstops ok this way, but maybe it should be different.I can tell you I am very happy to have this forum as I could not have gottento the point I am without the many usefull comments from folks on the list.I greatly appreciate everyone who has helped. But truth is if I cant get towork better then I have I may have to ditch the effort and bite the 70Kbullet. Its compounded by using 3 developers time for two months to yield ananswer that my boss may just fire me for. I figured since my first testshowed I could get data faster on the postgres box that I could with enoughstudy get all our data to go faster, but I am afraid I have not been verysuccessful.My failure is not a reflection postgres as you mentioned it is definatleygreat at some things. I have 90 some views not to mention as many storedprocedures that have been converted. I wrote an app to move the data and itworks great. But if it too slow I just will not be able to use forproduction.JoelJudging postgresql on one single data point (count(*) performance) isquite unfair.Unless your system only operates on static data and isused to mostly do things like counting, in which case, why are you usinga database?PostgreSQL is a great fit for certain loads, and a poor fit for others.Are you going to have lots of people updating the database WHILE theselect count(*) queries are running?Are you going to be doing other,more interesting things than simply counting?If so, you really shouldbuild a test case that emulates what you're really going to be doingwith the system.I've found that the poor performance of aggregates in PostgreSQL isgenerally more than made up for by the outstanding behaviour it exhibitswhen under heavy parallel load.Note that the basic design of PostgreSQL's MVCC system is such thatwithout using some kind of trigger to maintain pre-calculated aggregateinformation, it will NEVER be as fast as most other databases at doingaggregates across large chunks of your data.---(end of broadcast)---TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not matchHave you posted your postgresql config files for the folks here to review? I can't say I can help you with that because I too can only read the docs and go from there. But for specific situations you need specific configs. I would think you can get more out of postgresql with a some time and help from the people around here. Though count(*) looks like it may be slow.

Re: [SQL] getting count for a specific querry

2005-04-08 Thread Bob Henkel
On Apr 8, 2005 3:42 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
On Fri, 2005-04-08 at 15:35, Bob Henkel wrote: On Apr 8, 2005 3:23 PM, Vivek Khera [EMAIL PROTECTED] wrote: On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote:  I set up the data on 4 10k scsi drives in a powervault and my wal on 2  15k  drives. I am using links to those from the install directory. It  starts and  stops ok this way, but maybe it should be different.  Your problem might just be the choice of using a Dell RAID controller. I have a 1 year old box connected to a 14 disk powervault (PowerEdge 2650) and it is dog slow compared to a dual opteron with 8 disks that is replacing it.It is all I/O for me, and the dell's just are not known for speedy I/O. Vivek Khera, Ph.D. +1-301-869-4449 x806 But that is relative I would think.Is the Dell RAID much faster than my desktop SATA drive with no RAID? I'm by any means as knowledgeable about I/O setup as many of you are but my 2 cents wonders if the Dell RAID is really that much slower than a competitively priced/speced alternative?Would Joel's problems just fade away if he wasn't using a Dell RAID?My experience with the 3i controllers (See my earlier post) was that myold Pentium Pro200x2 machine with 512 meg ram and a generic Ultra WideSCSI card and a half dozen drives running software RAID 5 was faster.Seriously.So was my P-II-350 desktop with the same controller, and anolder Dual P-III-750 with only UltraSCSI running in a RAID-1 mirror set.The 3I is REALLY slow (or at least WAS slow) under linux.
Interesting... Maybe Joel after a weekend ofrest can try it on a different setup even if that different setup is just a power users development machine to see if he has same or worse timing results. Be wonderful if it magically sped up.