Re: index usage (and foreign keys/triggers)

Stephan Szabo wrote:

>>If I lower the random_page_cost to about 2 the index is being used
>>instead of seq scan. Is it reasonable to have such a setting on a
>>production server? random_page_cost = 2 is good for this particular
>>query, but could it have negative effect on other queries?
>
>
> It's possible since it might make other queries use an index when the
> sequence scan is better. It's probably worth doing some testing with the
> setting.

Ok. I'll do some testing and see what seems to work best for us.

>>>be lower, or that perhaps there's some level of clustering in the data
>>>that's not being picked up. You might want to try raising the
>>>number of statistics buckets and re-analyzing just to see if that helps.
>>
>>I'm afraid I'm a bit too new at this kind of tweaking... do you mean the
>>"default_statistics_target"? In that case I tried to raise it from the
>>default 10 to as high as 45, but without any other result than vacuum
>>analyze being slower. Did I understand your suggestion right?
>
>
> I'd thought about doing it with ALTER TABLE ALTER COLUMN SET STATISTICS,
> but I would think that it would probably have worked with default as well.

What exactly does this setting do and how does it affect the
planner/optimizer? I couldn't find much about this in the docs.

> Is it possible that the data has local clustering on the field (many
> rows with the same value stuck together) while not being terribly ordered
> overall? That's a case that the statistics don't really cover right now
> (there have been some discussions of this in the past)

How can I find this out? A simple "select * from login" and just browse
the result, or is there any automated way to analyze this?

Thanks,
Patrik Kudo