Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSQq4-0003vj-Tm for pgsql-sql@postgresql.org; Sun, 28 Oct 2012 11:15:32 +0000 Received: from authsmtp09.register.it ([81.88.48.59] helo=authsmtp.register.it) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSQpz-0004Q2-Of for pgsql-sql@postgresql.org; Sun, 28 Oct 2012 11:15:32 +0000 Received: (qmail 3087 invoked from network); 28 Oct 2012 11:15:26 -0000 Received: from unknown (HELO Moon) (oliveiros.cristina@asperger-talents.com@85.138.163.54) by authsmtp.register.it with ESMTPA; 28 Oct 2012 11:15:26 -0000 Message-ID: <0AE7DCC5786B49B69CA492A1C5BCC434@Moon> From: "Oliveiros d'Azevedo Cristina" To: "Scott Marlowe" , "Mark Fenbers" Cc: References: <508C75D1.2070906@noaa.gov><508C90B5.3060308@noaa.gov> Subject: Re: complex query Date: Sun, 28 Oct 2012 11:15:27 -0000 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157 X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201210/53 X-Sequence-Number: 36924 Hi, Scott. I'd like to kick in this thread to ask you some advice, as you are experienced in optimizing queries. I also use extensively joins and unions (less than joins though). Anyway, my response times are somewhat behind miliseconds, they are situated on seconds range, and sometimes they exceed one minute. I have some giant tables with over 100 000 000 records collected for more than 6 years. Most of my queries are made over recent data, so I'm considering partitioning the tables. But I believe that my problem arises from misplaced indexes... I have an index on every PRK. But if the join is not made using the PRKs, perhaps, should I place an index also on the joined columns? The application is not a hard real time one, but if you can do it much faster than I do, then I'm positive that I must have been doin something wrong. Could you please let me know about your thoughts on this? Thanks in advance Best, Oliver ----- Original Message ----- From: "Scott Marlowe" To: "Mark Fenbers" Cc: Sent: Sunday, October 28, 2012 2:20 AM Subject: Re: [SQL] complex query > On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers > wrote: >> I'd do somethings like: >> >> select * from ( >> select id, sum(col1), sum(col2) from tablename group by yada >> ) as a [full, left, right, outer] join ( >> select id, sum(col3), sum(col4) from tablename group by bada >> ) as b >> on (a.id=b.id); >> >> and choose the join type as appropriate. >> >> Thanks! Your idea worked like a champ! >> Mark > > The basic rules for mushing together data sets is to join them to put > the pieces of data into the same row (horiztonally extending the set) > and use unions to pile the rows one on top of the other. > > One of the best things about PostgreSQL is that it's very efficient at > making these kinds of queries efficient and fast. I've written 5 or 6 > page multi-join multi-union queries that still ran in hundreds of > milliseconds, returning thousands of rows. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >