From: | "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)asperger-talents(dot)com> |
---|---|
To: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, "Mark Fenbers" <mark(dot)fenbers(at)noaa(dot)gov> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: complex query |
Date: | 2012-10-28 11:15:27 |
Message-ID: | 0AE7DCC5786B49B69CA492A1C5BCC434@Moon |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)marlowe(at)gmail(dot)com>
To: "Mark Fenbers" <mark(dot)fenbers(at)noaa(dot)gov>
Cc: <pgsql-sql(at)postgresql(dot)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(dot)fenbers(at)noaa(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-10-28 14:54:12 | Re: pull in most recent record in a view |
Previous Message | Scott Marlowe | 2012-10-28 02:20:50 | Re: complex query |