pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (4+ messages)

Message-ID: <1059158034.1183.43.camel@taz>
Permalink:  ../1059158034.1183.43.camel@taz/
Also on:    postgresql.org/message-id/1059158034.1183.43.camel@taz

 · 

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 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