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.marl...@gmail.com>
To: "Mark Fenbers" <mark.fenb...@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.fenb...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
