Is Optimizer smart enough?

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