SQL Query Question

[jamoraquai]

Hi guys!!!

Any idea how i can optimize this sql query?

select (sum(ac1.insertions) + sum(ac2.insertions) + sum(ac3.insertions)) as insertions, ac1.time_stamp from ac1, ac2, ac3 where ac1.time_stamp = ac2.time_stamp and ac2.time_stamp = ac3.time_stamp and ac1.time_stamp between '07/22/2003 09:00:00' and '07/22/2003 16:00:00'group by ac1.time_stamp;

result:

insertions | time_stamp
------------+-------------------------
1200 | 07/22/2003 09:03:00 HKT
1350 | 07/22/2003 09:04:00 HKT
1500 | 07/22/2003 09:05:00 HKT
1650 | 07/22/2003 09:06:00 HKT
1800 | 07/22/2003 09:07:00 HKT
1950 | 07/22/2003 09:08:00 HKT
2100 | 07/22/2003 09:09:00 HKT
:
:
:

Thanks in advance everyone. hope you can help me with this coz the query really takes a long time to process....

 

[ceco]

do you have indexes on the time_stamp fields ?

if not try

create index ac1_time_stamp ON ac1(time_stamp);
create index ac2_time_stamp ON ac2(time_stamp);
create index ac3_time_stamp ON ac3(time_stamp);

and then
analyze ac1;
analyze ac2;
analyze ac3;

it may help, can't tell for sure

 

[jamoraquai]

Thanks for your reply ceco. Do you think my query is ok and it just lacks creation of index on each table?

 

[ceco]

I don't see any other obvious reason for the slow query.

you can use

EXPLAIN select (sum(ac1.insertions) + sum(ac2.insertions) + sum(ac3.insertions)) as insertions, ac1.time_stamp from ac1, ac2, ac3 where ac1.time_stamp = ac2.time_stamp and ac2.time_stamp = ac3.time_stamp and ac1.time_stamp between '07/22/2003 09:00:00' and '07/22/2003 16:00:00'group by ac1.time_stamp;

to see what happens and deside whether the indexes help or not

ANALYZE is also needed from time to time, you may already do it with the VACUUM-ing process, if not consider doing vacuum analyze from time to time

 

[osensei]

If I remember correctly from the old days of rule based optimizers you query would be processed as follows;

select (sum(ac1.insertions) + sum(ac2.insertions)
+ sum(ac3.insertions)) as insertions,
ac1.time_stamp
from ac1,
ac2,
ac3
where ac1.time_stamp = ac2.time_stamp
and ac2.time_stamp = ac3.time_stamp
and ac1.time_stamp between '07/22/2003 09:00:00' and '07/22/2003 16:00:00'group by ac1.time_stamp;

The equa-joins on ac1->ac2 then ac2->ac3 will be processed first. Indexes would definitley help on the timestamp fields here. Then a range scan would be executed on the ac1 timestamp.

I'm not sure if the postgres sql is rule based or not. If it is then rearranging the where clause will produce much needed optimization.

If the equa-joins produce a large number of rows then start by narrowing the field. In your <where clause>, move the <between clause> to the front. This will reduce the number of rows being processed from table ac1 at the onset and thereby reduce the number of operations needed to build the rest of the result set.

Where ac1.time_stamp
between '07/22/2003 09:00:00'
and '07/22/2003 16:00:00'

Now that you have narrowed ac1, the order of the equa-joins gain importance. Next do:
and ac2.time_stamp = ac1.time_stamp

Notice the reversal. This is because the <between clause has caused the results from table ac1 to cache. The reversal causes the index on ac2.time_stamp to be used against the cached results from ac1.

Now reverse again for the last <and condition>. The same is true here now ac2 is cached and can be used to compare against the index on ac3.

ac3.time_stamp = ac2.time_stamp

In rule bases optimization indexes are only used for colums in <where clauses> when:

1- columns that are on the left had side of the &quot;=&quot;
2- columns that are not arguments to function
(i.e. tochar(column))
3- columns are not operands within a range
(ie, >, < >=, <=, !=, in, not in, between)