On Apr 20, 2014 4:21 AM, "Torsten Förtsch" <torsten.foertsch@xxxxxxx> wrote:
>
> On 20/04/14 12:08, Thomas Kellerer wrote:
> >> Example:
> >>
> >> btree (fmb_id, action_type)
> >> btree (fmb_id)
> >>
> [...]
> >
> > Btw: in the above example the second one is definitely not needed.
> > Any query that uses the second one (single column) can also use the
> > first one.
>
> I know.
>
> But the single column index is 3534 MB the 2-column one 4963 MB. The
> number of rows per distinct fmb_id is very small, usually 1 or 2. So, if
> a query looks for "fmb_id=A and action_type=B", it has to filter out
> only one row if the first index is not available. Hence, I thought maybe
> the lower size of the index and the higher compactness per page could
> outweigh the more direct access provided by the 2-column index.
I don't think there is a supported way to do that. And since the quasi dropped index still has to be maintained (and so probably kept in cache), the benefits of dropping it may not be observable anyway by your proposed experiment, unless the table is read only.
Cheers,
Jeff
