Thread: Creating a field that uses totals other fields?

I am wanting to create a table that will sum up the total of an order and 
automatically put value in the specified field. An example of the table is 
below. Any ideas on how to do this.

CREATE TABLE  sales_info (
sales_id serial PRIMARY KEY,
prod_id int REFERENCES product (prod_id),
qty int,
unit_price numeric(4,2),
sub_total numeric(4,2),
tax numeric(4,2),
shipping numeric(4,2),
grand_total numeric(4,2));
The grand total is where I want the sum of the unit_price, tax, and 
shipping price entered automatically. Thanks for any help.


--Sloan

I am running into a serious performance issue with some basic queries.

If I run something like
  select * from "Calls" WHERE               ( ("CallType" = 'LONG DIST' ))

The search takes about 15 seconds

if I run   select * from "Calls" WHERE       ( ( "DateOfCall"='06/19/02') )       AND ( ( "CallType" = 'LONG DIST' ))
[DateOfCallis a DateTime field]
 

it takes 15 minutes!

both columns are indexed individually and expalain reports and Index scan for both
queries.

Any pointers where to look next?

Running pg 7.2 on RH

Thanks,
-Aaron Held

On Wed, 18 Sep 2002, Aaron Held wrote:

> I am running into a serious performance issue with some basic queries.
>
> If I run something like
>
>    select * from "Calls" WHERE
>                 ( ("CallType" = 'LONG DIST' ))
>
> The search takes about 15 seconds
>
> if I run
>     select * from "Calls" WHERE
>         ( ( "DateOfCall"='06/19/02') )
>         AND ( ( "CallType" = 'LONG DIST' ))
>    [DateOfCall is a DateTime field]
>
> it takes 15 minutes!
>
> both columns are indexed individually and expalain reports and Index scan for both
> queries.
>
> Any pointers where to look next?

Are they using the same index? Have you done analyze
recently?

Thanks,
Changing '0/19/01' to '0/19/01'::date gave me a subjective 50% speedup.A ran a bunch of queries w/ explain and I
noticedthat some 
 
combinations did not use the indexes and went right to seq scan.  All of 
the where clause args are indexed.
# SET enable_seqscan to FALSE ;forced the use of an Index and sped things up greatly.

I am not sure why it made the switch.  The load on the server seems to 
affect the performance, but I am seeing it more on the production server 
with 100 million rows as opposed to the development server with only 
about 6 million.  I need to buy more drives and develop on a larger data 
set.

Thanks for the help,
-Aaron Held

Chris Ruprecht wrote:
> Aaron,
> 
> On Wed September 18 2002 17:17, Aaron Held wrote:
> 
>>I am running into a serious performance issue with some basic queries.
>>
>>If I run something like
>>
>>   select * from "Calls" WHERE
>>                ( ("CallType" = 'LONG DIST' ))
>>
>>The search takes about 15 seconds
>>
>>if I run
>>    select * from "Calls" WHERE
>>        ( ( "DateOfCall"='06/19/02') )
>>        AND ( ( "CallType" = 'LONG DIST' ))
>>   [DateOfCall is a DateTime field]
> 
> 
> try ... "DateOfCall" = '2002-06-19'::date ...
> 
> Best regards,
> Chris

Aaron,

>     # SET enable_seqscan to FALSE ;
>     forced the use of an Index and sped things up greatly.
>
> I am not sure why it made the switch.  The load on the server seems to
> affect the performance, but I am seeing it more on the production server
> with 100 million rows as opposed to the development server with only
> about 6 million.  I need to buy more drives and develop on a larger data
> set.

What version are you using?

I'd have 3 suggestions:
1) ANALYZE, ANALYZE, ANALYZE.  Then check if the row estimates made by EXPLAIN
seem accurate.
2) Modify your postgresql.conf file to raise the cost of seq_scans for parser
estimates.
3) Test this all again when 7.3 comes out, as parser estimate improves all the
time.

--
-Josh BerkusAglio Database SolutionsSan Francisco

I am running pg 7.2 the PG reference build.

Thanks for the ANALYZE tip,  it led me to a answer.

This database gets a monthly update and it read only until the next 
update.  I ANALYZE once after each update.  Since the data does not 
change I should not need to ANALYZE again afterwards.

I mentioned this to the dbadmin that manages the data and found out one 
of the other users UPDATED some of the columns the morning that I was 
seeing this behavior.

I'll reANALYZE and see what happens.

Thanks,
-Aaron Held


Josh Berkus wrote:
> Aaron,
> 
> 
>>    # SET enable_seqscan to FALSE ;
>>    forced the use of an Index and sped things up greatly.
>>
>>I am not sure why it made the switch.  The load on the server seems to 
>>affect the performance, but I am seeing it more on the production server 
>>with 100 million rows as opposed to the development server with only 
>>about 6 million.  I need to buy more drives and develop on a larger data 
>>set.
> 
> 
> What version are you using?
> 
> I'd have 3 suggestions:
> 1) ANALYZE, ANALYZE, ANALYZE.  Then check if the row estimates made by EXPLAIN 
> seem accurate.
> 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser 
> estimates.
> 3) Test this all again when 7.3 comes out, as parser estimate improves all the 
> time.
> 

Aaron,

> This database gets a monthly update and it read only until the next
> update.  I ANALYZE once after each update.  Since the data does not
> change I should not need to ANALYZE again afterwards.

Actually, if the database is read-only between updates, you should:

ANALYZE
VACUUM FULL FREEZE

... between data updates, which will give you the best performance possible.
But not if the data is actually going to be edited, even slightly.

See the online manual entries about VACUUM for an explanation.

--
-Josh BerkusAglio Database SolutionsSan Francisco