From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: index questions
Date: 25 Jul 2003 15:33:54 -0300
Message-ID: <1059158034.1183.43.camel@taz> (raw)
In-Reply-To: <200307251028.27261.josh@agliodbs.com>
References: <1059151963.1183.14.camel@taz>
<200307251028.27261.josh@agliodbs.com>
what you say is that the index is not effective because of its size, but
it would still be used *if* the conditions are right... In this case, I
care about performance, not space.
But what you say about the index not being good because 100% of rows
match the condition confirms what I suspected.
Thanks for your help.
On Fri, 2003-07-25 at 14:28, Josh Berkus wrote:
> 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.
Attachments:
signature.asc (application/pgp-signature, 186B) download
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: franco@akyasociados.com.ar, josh@agliodbs.com
Subject: Re: index questions
In-Reply-To: <1059158034.1183.43.camel@taz>
References: <1059151963.1183.14.camel@taz> <200307251028.27261.josh@agliodbs.com> <1059158034.1183.43.camel@taz>
* 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