X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F0B953A3B06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 29 Oct 2004 18:47:00 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 14965-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 29 Oct 2004 17:46:55 +0000 (GMT)
Received: from mail.logi-track.com (www.logi-track.com [213.239.193.212])
	by svr1.postgresql.org (Postfix) with ESMTP id A886D3A3B03
	for <pgsql-sql@postgresql.org>; Fri, 29 Oct 2004 18:46:55 +0100 (BST)
Received: from kingfisher.intern.logi-track.com (J0b0d.j.pppool.de
	[85.74.11.13])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.logi-track.com (Postfix) with ESMTP id AA0653016C;
	Fri, 29 Oct 2004 19:46:58 +0200 (CEST)
Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1])
	by kingfisher.intern.logi-track.com (Postfix) with SMTP id 5105E16E15F;
	Fri, 29 Oct 2004 19:46:51 +0200 (CEST)
Date: Fri, 29 Oct 2004 19:46:49 +0200
From: Markus Schaber <schabios@logi-track.com>
To: "andre.toscano" <andre.toscano@uol.com.br>
Cc: "pgsql-sql" <pgsql-sql@postgresql.org>
Subject: Re: extra info - curious delay on view/where
Message-ID: <20041029194649.4711e92d@kingfisher.intern.logi-track.com>
In-Reply-To: <I6AUP1$D20A31AD6BA04587AF2C81E6DEC05C48@uol.com.br>
References: <I6AUP1$D20A31AD6BA04587AF2C81E6DEC05C48@uol.com.br>
Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?=
X-Mailer: Sylpheed-Claws 0.9.12b (GTK+ 1.2.10; i386-pc-linux-gnu)
X-Face: Nx5T&>Nj$VrVPv}sC3IL&)TqHHOKCz/|)R$i"*r@w0{*I6w;
	UNU_hdl1J4NI_m{IMztq=>cmM}1gCLbAF+9\#CGkG8}Y{x%SuQ>1#t:;
	Z(|\qdd[i]HStki~#w1$TPF}:0w-7"S\Ev|_a$K<GcL?@F\BY,ut6tC0P<$eV&ypzvlZ~R00!A
X-PGP-Key: http://schabi.de/pubkey.asc
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/210
X-Sequence-Number: 19313

Hi, Andre,

On Thu, 28 Oct 2004 11:53:25 -0300
"andre.toscano" <andre.toscano@uol.com.br> wrote:

> Could an INDEX speed up that SELECT?

> > > CREATE VIEW "stock_available" as
> > >   SELECT * FROM stock_details
> > >   WHERE available =3D true AND visible =3D true AND
> > >   location not in (SELECT descr FROM ignored);

Yes, I'm shure.=20

I would try to create (on the underlying table) a conditional index on
the column "location" with the condition "available =3D true AND visible =3D
true".

As often, the acutal effect depends on the count of rows the query
returns compared to the total rows in the table. And it would be helpful
to know the typical queries (especially the rows in the WHERE clauses)
to give additional hints on creating indices. A matching index also
potentially speeds up ORDER BY queries.

HTH,
Markus



--=20
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 z=FCrich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com