Re: Aggregate query for multiple records

shouldn't take that long, I would think.
You have indexes on wid and date?

Troy

>
> Hello, I am new to the list, my apology if this question is beyond the
> scope or charter of this list.
>
> My questions is:
> What is the best method to perform an aggregate query to calculate
> sum() values for each distinct wid as in the example below, but except
> for all wid's (not just WHERE wid='01/1-6-1-30w1/0').
>
> Also, performance wise, would it be better to build a function for this
> query. The table has 9 million records and these aggregate queries
> take hours.
>
>
> SELECT
> SUM(oil) as sumoil, SUM(hours) as sumhours,
> FROM
> (SELECT oil, hours prd_data WHERE wid='01/1-6-1-30w1/0'
> ORDER BY date LIMIT 6) subtable
> ;
>
>
> Table description:
> Table "prd_data"
> Column | Type | Modifiers
> --------+-----------------------+-----------
> date | integer |
> hours | real |
> oil | real |
> gas | real |
> water | real |
> pwid | integer |
> wid | character varying(20) |
> year | smallint |
> Indexes: wid_index6
>
>
> Actual table (prd_data), 9 million records:
>
> date | hours | oil | gas | water | pwid | wid | year
> --------+-------+-------+------+-------+------+-----------------+------
> 196507 | 360 | 159.4 | 11.3 | 40.9 | 413 | 01/1-1-1-31w1/0 | 1965
> 196508 | 744 | 280 | 20 | 27.2 | 413 | 01/1-1-1-31w1/0 | 1965
> 196509 | 360 | 171.1 | 11.4 | 50.4 | 413 | 01/1-1-1-31w1/0 | 1965
> 196510 | 744 | 202.1 | 25 | 89.8 | 413 | 01/1-1-1-31w1/0 | 1965
> 196512 | 744 | 201.3 | 23.8 | 71.9 | 413 | 01/1-1-1-31w1/0 | 1965
> 196511 | 720 | 184 | 17.6 | 78.9 | 413 | 01/1-1-1-31w1/0 | 1965
> 196610 | 744 | 99.8 | 15.4 | 53.7 | 413 | 01/1-1-1-31w1/0 | 1966
> 196612 | 744 | 86 | 12.8 | 36.1 | 413 | 01/1-1-1-31w1/0 | 1966
> 196611 | 720 | 86 | 12.6 | 41.7 | 413 | 01/1-1-1-31w1/0 | 1966
> 196601 | 744 | 191.6 | 22.6 | 50.7 | 413 | 01/1-1-1-31w1/0 | 1966
> 200301 | 461 | 68.8 | 0 | 186.3 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200310 | 740 | 446.3 | 0 | 563.1 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200306 | 667 | 92.1 | 0 | 968.8 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200304 | 0 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200308 | 457 | 100.7 | 0 | 82.8 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200307 | 574 | 78 | 0 | 752 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200312 | 582 | 360.9 | 0 | 569 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200311 | 681 | 260.8 | 0 | 563.9 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200305 | 452 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003
> 200309 | 637 | 244.6 | 0 | 193.8 | 47899 | 9G/6-1-50-24w3/0 | 2003
> (20 rows)
>
>
>
> Thanks,
>
> --
> Scott A. Gerhardt, P.Geo.
> Gerhardt Information Technologies
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>