| Lists: | pgsql-hackers |
|---|
| From: | tmorelli(at)tmorelli(dot)com(dot)br |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Is Optimizer smart enough? |
| Date: | 2006-01-10 23:50:23 |
| Message-ID: | 20060110235023.B2CEEF368E@smtpi01.infolink.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | tmorelli(at)tmorelli(dot)com(dot)br |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Is Optimizer smart enough? |
| Date: | 2006-01-11 05:08:45 |
| Message-ID: | 8514.1136956125@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
tmorelli(at)tmorelli(dot)com(dot)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