Re: [SQL] indexes

Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl> writes:
> 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)
>

I think the real problem here is that the optimizer thinks your tables
are small (notice the size=1 estimates in the inner loop). Have you
done a VACUUM lately? You need that to update the statistics that the
optimizer uses.

Unless you are dealing with very small tables, you don't want to see
nested-loop joins (that means scanning the lower table once for each
tuple in the upper table!). You want to see merge joins or hash joins.

Vadim's suggestion of a better-adapted index was a good one, but I
wonder whether the speedup you saw wasn't just a side effect from
CREATE INDEX having updated the optimizer's stats, so that it stopped
using nested loops...

regards, tom lane

> Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl> writes:
> > 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)
> >
>
> I think the real problem here is that the optimizer thinks your tables
> are small (notice the size=1 estimates in the inner loop). Have you
> done a VACUUM lately? You need that to update the statistics that the
> optimizer uses.

Yea - I've just done VACUUM during my efforts to speed up query (btw.
should I do something with notices from VACUUM like that one:
NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
HEAP' (1787))

> Unless you are dealing with very small tables, you don't want to see
> nested-loop joins (that means scanning the lower table once for each
> tuple in the upper table!). You want to see merge joins or hash joins.
>
> Vadim's suggestion of a better-adapted index was a good one, but I
> wonder whether the speedup you saw wasn't just a side effect from
> CREATE INDEX having updated the optimizer's stats, so that it stopped
> using nested loops...
>
I have too less experience to know at what quantity of records use which
join.
In this case table ent has 1304 rows and table binds 1787.
But without Vadim's suggestion I've got still similar results - sometimes
optimizer has used index on e1 and on b1, sometimes on e1 and e2, but
never on e1,b1 and e2 at once (note: this was join on ent e1, binds b1 and
ent e2) and whole query was performed at cost about 70. After rebuilding
index at cost 6-7 (what is enough for me)
Rem

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

Remigiusz Sokolowski wrote:
>
> Yea - I've just done VACUUM during my efforts to speed up query (btw.
> should I do something with notices from VACUUM like that one:
> NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
> HEAP' (1787))

Ops. Re-build binds_idx! And use latest 6.5 snapshot if you use
6.5 beta...

Vadim

> >
> > Yea - I've just done VACUUM during my efforts to speed up query (btw.
> > should I do something with notices from VACUUM like that one:
> > NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
> > HEAP' (1787))
>
> Ops. Re-build binds_idx! And use latest 6.5 snapshot if you use
> 6.5 beta...
>
> Vadim

I use ver. 6.3.2 and have rather little influence on which version is used
- my admin decide about such things.
Is this possible to solve this manually?
Rem

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