On Tue, 8 Jul 2003, Martin Foster wrote:
> Stephan Szabo wrote:
> > The row estimate is high. How many rows meet the various conditions and
> > some of the combinations? And how many rows does it estimate if you do a
> > simpler query on those with explain?
> > I still think some variety of multi-column index to make the above index
> > conditions would help, but you'd probably need to play with which ones
> > help, and with the cost cut for the limit, I don't know if it'd actually
> > get a better plan, but it may be worth trying a bunch and seeing which
> > ones are useful and then dropping the rest.
> At any given point in time you would not expect to see much more then 30
> posts applying for a time based search. That is primarily a result of
> having more then one room for which posts are attached to, and then some
> posts exist just to show people are there et cetera.
> Simpler queries seem to do quiet well. That view makes use of the same
> table and seems to have no performance impact from doing as such, and
> the position based search is considerably faster.
Well, the reason I asked is to see both whether the estimates for the
various columns were somewhere near reality (if not, then you may need to
raise the statistics target for the column) which might affect whether
it'd consider using a multi-column index for the conditions and sort
rather than the index scan it was using.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster