From: Vadim Mikheev <vadim@krs.ru>
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] indexes
Date: Mon, 31 May 1999 19:54:24 +0800
Message-ID: <37527870.9ACEAB28@krs.ru> (raw)
References: <Pine.GS4.4.02A.9905311102450.26326-100000@netra.gdansk.sprint.pl>
Remigiusz Sokolowski wrote:
>
> > > Hi!
> > > I try to optimize following query
> > > SELECT DISTINCT e1.name_ent AS nazwa_grupy
> > > FROM ent e1, binds b1, ent e2
> > > WHERE e1.id_ent=b1.id_parent AND b1.id_child=e2.id_ent AND b1.id_links=0
> > > AND e2.name_ent='SERWIS';
> > >
> > > Table = binds2_idx
> > > +--------------------+----------------------------------+------+
> > > |Field | Type |Length|
> > > +--------------------+----------------------------------+------+
> > > | id_parent | int4 |4 |
> > > | id_child | int4 |4 |
> > > | id_links | int4 |4 |
> > > +--------------------+----------------------------------+------+
> >
> > Try to create index on binds (id_links, id_parent, id_child)
> >
> > Vadim
>
> Wow - works great - big thanks!
> Could You throw some hints - why index should look like as You wrote?
> I looked in docs, but there is no clues how to build indices (or I just
> didn't find them)
Index can be used only if first index key is defined. In your query
b1.id_parent (your old first index key) is used in join clause and
so binds index could be used only in inner plan: after a tuple
is fetched from e1 (outer plan) making b1.id_parent defined, but
optimizer decided that this is not right way.
b1.id_links has constant value and so binds index with id_links as
first key may be used in outer plan (scanned first). Other keys of
new binds index will not be used in index scan of this query anyway.
After a binds tuple is fetched, keys for e1/e2 indices is defined and
so these indices can be used, and more of that - both keys of
e2 index will be used in scan.
Vadim
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-sql@postgresql.org
Cc: vadim@krs.ru, rems@gdansk.sprint.pl
Subject: Re: [SQL] indexes
In-Reply-To: <37527870.9ACEAB28@krs.ru>
* 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