pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sven Willenberger <sven@dmv.com>
To: pgsql-performance@postgresql.org
Subject: Overhead of dynamic query in trigger
Date: Fri, 03 Dec 2004 22:46:45 -0500
Message-ID: <41B13325.3070202@dmv.com> (raw)

(Originally asked in [General], realized that it would probably be 
better asked in [Perform]:

I am curious as to how much overhead building a dynamic query in a
trigger adds to the process. The example:

Have a list of subcontractors, each of which gets unique pricing. There 
is a total of roughly 100,000 items available and some 100 
subcontractors. The 2 design choices would be 100 tables (one for each 
sub) at 100,000 rows or 1 table with 10,000,000 rows.

Choice 1:
table has item number (indexed) and price

Choice 2:
table has subcontractor id, item number, and price; index on
(subcontractorid, item number).

Table of orders would have a trigger to insert line item cost:
-----------------------------------
Trigger Choice 1:
Select into thetable lookupprice from subcontractors where
subcontractorid = NEW.subcontractorid;

thequery := ''Select price from '' || thetable.lookupprice || '' where
itemnumber = '' || NEW.itemnumber;

FOR therow IN EXECUTE thequery LOOP
	NEW.itemcost := therow.price;
END LOOP;
RETURN NEW;
-----------------------------------
Trigger Choice 2:
Select into thetable lookupprice from subcontractors where
subcontractorid = NEW.subcontractorid;

Select into therow price from mastertable where subcontractorid =
NEW.subcontractorid and itemnumber = NEW.itemnumber;

NEW.itemcost := therow.price;
RETURN NEW;
-----------------------------------

Doing a select from the command line, the mastertable method (with id
and partno index) is faster than looking up a single item in a named
table (with partno index). At what point would Trigger Choice 2 fall
behind performance with Trigger Choice 1 (if ever)? Is there a way to
analyze the performance of dynamic queries? If I had only 10
subcontractors or if I had 1000 subcontractors, at what point is the
overhead of building/executing a dynamic query negated by the amount of
time to look up both the subid and part number in one massive table?

Thanks,

Sven



Message-ID: <41B13325.3070202@dmv.com>
Permalink:  ../41B13325.3070202@dmv.com/
Also on:    postgresql.org/message-id/41B13325.3070202@dmv.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: sven@dmv.com
  Subject: Re: Overhead of dynamic query in trigger
  In-Reply-To: <41B13325.3070202@dmv.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox