Tom-
Thanks for the info-
Based on your response plus some local issues, we're going to work around
this by simply creating another column containing the results of the
function & then index the column. That gets the results we want without
tweaking something we may regret later.
Stats for function indexes would be nice, so add our vote for it to wherever
such things are tallied to come up with priorities.
Regards,
-Nick
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Wednesday, June 26, 2002 10:37 PM
> To: nickf@ontko.com
> Cc: pgsql-admin
> Subject: Re: [ADMIN] Are statistics gathered on function indexes?
>
>
> "Nick Fankhauser" <nickf@ontko.com> writes:
> > [see subject]
>
> Nope, they ain't. I agree they should be.
>
> > Can someone tell me how the cost is estimated for retrieving a
> column based
> > on a function that is indexed?
>
> It falls back to a default selectivity estimate, which is something
> like 1% or 0.5% (depending on which version you are running).
>
> > Also, even with 2168 rows to gather, my experience based on cases where
> > several thousand rows really are returned indicates that the index would
> > still be a good choice. Is there a way to make the planner
> favor index scans
> > a bit more? (Other than the drastic set enable_seqscan to off.)
>
> I'd suggest reducing random_page_cost; we've seen a number of anecdotal
> reports that the default of 4.0 is too high, though nothing systematic
> enough to refute the experiments I did to get that number awhile back.
> (IMHO anyway. Others may differ.)
>
> regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>