From: Josh Berkus <josh@agliodbs.com>
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: pgsql-performance@postgresql.org
Subject: Re: index questions
Date: Fri, 25 Jul 2003 10:28:27 -0700
Message-ID: <200307251028.27261.josh@agliodbs.com> (raw)
In-Reply-To: <1059151963.1183.14.camel@taz>
References: <1059151963.1183.14.camel@taz>
Franco,
> CREATE INDEX sales_k1 ON sales(clientId, branchId, productId,
> employeeId, saleDate, price, qty);
A 7-column index is unlikely to be effective -- the index will be almost as
large as the table. Try indexing only the first 3-4 columns instead.
> I want to make a function that returns the FIRS saleId of the sale that
> matches some conditions. I will
> always receive the Client Id, but not always the other arguments (sent
> as NULLs).
Well, keep in mind that your multi-column index will only be useful if all
columns are queried starting from the left. That is, the index will be
ignored if you have a "where productId = x" without a "where branchid = y".
> CREATE OR REPLACE FUNCTION findSale(INTEGER, INTEGER, INTEGER, INTEGER)
> RETURNS INTEGER AS '
> DECLARE
> a_clientId ALIAS FOR $1;
> a_branchId ALIAS FOR $1;
> a_productId ALIAS FOR $1;
> a_employeeId ALIAS FOR $1;
Your aliases are wrong here.
> branchId=coalesce(a_branchId, branchId) AND /*branchId is null?
> anything will be ok*/
> productId=coalesce(a_productId, productId) AND /*productId is
> null? anything will be ok*/
On a very large table this will be very inefficient. you'll be comparing the
productid, for example, even if no productid is passed ... and the index
won't do you any good because the planner should figure out that 100% of rows
match the condition.
Instead, I recommend that you build up a dynamic query as a string and then
pass only the conditions sent by the user. You can then EXECUTE the query
and loop through it for a result.
Of course, YMMV. My approach will require you to create more indexes which
could be a problem if you have limited disk space.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, franco@akyasociados.com.ar
Subject: Re: index questions
In-Reply-To: <200307251028.27261.josh@agliodbs.com>
References: <1059151963.1183.14.camel@taz> <200307251028.27261.josh@agliodbs.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