indexes

From: Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl>
To: pgsql-sql(at)postgresql(dot)org
Subject: indexes
Date: 1999-05-31 08:42:02
Message-ID: Pine.GS4.4.02A.9905311027530.26326-100000@netra.gdansk.sprint.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Is it possible to get better results? From explain notes it looks, that
engine uses indexes on ent table, but I can't constrain it to use any
index on binds table - this causes main problem.
Or may be query should look in other way?

EXPLAIN gives following information:
NOTICE: QUERY PLAN:
Unique (cost=77.02 size=0 width=0)
-> Sort (cost=77.02 size=0 width=0)
-> Nested Loop (cost=77.02 size=1 width=28)
-> Nested Loop (cost=74.97 size=1 width=12)
-> Seq Scan on b1 (cost=72.97 size=1 width=8)
-> Index Scan on e2 (cost=2.00 size=1 width=4)
-> Index Scan on e1 (cost=2.05 size=1304 width=16)

and I have following indexes
Table = ent2_idx
+--------------------+--------------------+------+
|Field |Type |Length|
+--------------------+--------------------+------+
| id_ent | int4 |4 |
| name_ent | text |var |
+--------------------+--------------------+------+

and
Table = binds2_idx
+--------------------+----------------------------------+------+
|Field | Type |Length|
+--------------------+----------------------------------+------+
| id_parent | int4 |4 |
| id_child | int4 |4 |
| id_links | int4 |4 |
+--------------------+----------------------------------+------+

TIA
Rem

-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems(at)gdansk(dot)sprint(dot)pl * *
-----------------------------------------------------------------*****----------

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Vadim Mikheev 1999-05-31 08:50:50 Re: [SQL] indexes
Previous Message Herouth Maoz 1999-05-31 08:24:26 Re: [SQL] Database Design question