Re: complex query

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" <scott.marlowe@gmail.com>
To: "Mark Fenbers" <mark.fenbers@noaa.gov>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, October 28, 2012 2:20 AM
Subject: Re: [SQL] complex query


> On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers <mark.fenbers@noaa.gov> 
> 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
>