Thread: Is Optimizer smart enough?

Hi,

Just for curiosity: suppose there is an excellent index frequently picked by
the optimizer. Suppose now that this index became extremelly fragmented with
thousands of updates. Without a REINDEX, will Optimizer still pick it? Or the
optimizer is smart enough to detect index fragmentation and discard it?

Is there something that I could do in btcostesimate function to detect
increasing index fragmentation?

best regards,

Eduardo Morelli

tmorelli@tmorelli.com.br writes:
> Just for curiosity: suppose there is an excellent index frequently picked by
> the optimizer. Suppose now that this index became extremelly fragmented with
> thousands of updates. Without a REINDEX, will Optimizer still pick it? Or the
> optimizer is smart enough to detect index fragmentation and discard it?

IIRC, btcostestimate is sensitive to the physical size of the index,
so it would catch the first-order effect of index bloat.  It wouldn't
notice index fragmentation in the sense of increasingly random location
of logically-sequential leaf pages.  But I'm not sure how much that
matters for typical situations.  If you have a huge fraction of dead
tuples, I'd say that index fragmentation is not your worst problem...
        regards, tom lane