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
>