Re: Not using index

Tom Lane wrote:
>
> There is certainly not a dependence on * as such.

Yes, that was pretty strange, but, not knowing the internals of the
optimizer, I wondered why a change in the target list could have changed
the decision of the optimizer.

> However, the estimated row width does affect the cost estimate for operations
> like SORT, where we have to guess how many rows will fit in memory. It looks
> to me like your example case is right near the boundary where the system
> thinks that index scan and sort are of roughly equal cost, so relatively
> small changes will push the choice in one direction or the other.

Yes this is what's happening. I progressively adding attributes to the
targets list and, at some point, the optimizer choosed the other
alternative.

> I'm assuming that you are complaining because one or the other of
> these plans is actually much cheaper than the other in your example.

Yes, more than 12:1 ratio.

> You have, however, carefully refrained from giving us any hint which.
> Care to fess up with more details?

Yes, of course, I hope the following statistics can help you :

This one is the result of vacuum on the table:

NOTICE: Pages 688: Changed 0, reaped 0, Empty 0, New 0; Tup 39100: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100, MaxLen 201; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.04s/3.37u sec.
NOTICE: Index telecom_settore: Pages 145; Tuples 39100. CPU 0.01s/0.07u
sec.
NOTICE: Index telecom_distretto: Pages 156; Tuples 39100. CPU
0.01s/0.06u sec.
NOTICE: Index telecom_regione: Pages 136; Tuples 39100. CPU 0.01s/0.06u
sec.

This one is with the index:
! system usage stats:
! 0.909758 elapsed 0.830000 user 0.050000 system sec
! [0.870000 user 0.060000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 282/14 [429/279] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 845 read, 0 written, buffer hit rate =
97.86%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

This one is without the index:
! system usage stats:
! 12.529637 elapsed 12.360000 user 0.150000 system sec
! [12.380000 user 0.190000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 508/360 [695/657] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 688 read, 1 written, buffer hit rate =
0.72%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

If you need other statistics/tests, just ask and I will be happy to help
you.

Bye!

--
Daniele

-------------------------------------------------------------------------------
Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------