Re: [SQL] indexes

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