Re: Not using index - Mailing list pgsql-hackers

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.comViaMezzera 29/A - 20030 - Seveso (MI) - Italy
 
-------------------------------------------------------------------------------