Tom-
Thanks for helping me work through my question about the effect of
n_distinct. Now that I understand how little this statistic affects the
query plan, I'm going to spend some time trying to puzzle it out more on my
own before coming back to the list for advice.
Thanks also for pointing out
Hi-
I tried to post this to the performance list, but that list seems to have a
problem at the moment. I think the question fits admin as well:
I have a table- called event with a field event_date_time that is indexed.
There are 1,700,000 rows in the table and 92,000 distinct values of
Nick Fankhauser [EMAIL PROTECTED] writes:
So the question is- how can I get a better estimate of n_distinct from
analyze?
If I alter the stats target as high as it will go, I get closer, but it
still shows the index to be about 1/2 as selective as it actually is:
AFAIK, estimating number of
AFAIK, estimating number of distinct values from a small sample is
inherently an ill-conditioned problem.
If I had been getting estimates all over the map, I'd have been a bit more
unconcerned, but what I'm seeing is a very consistent number that also
increases and tends to be more consistent
Nick Fankhauser [EMAIL PROTECTED] writes:
when I do a \d on pg_stats, it appears that pg_statistic.starelid matches up
with pg_class.oid, but apparently this is not the case.
It certainly should be the case. starelid matches to pg_class.oid and
staattnum matches to pg_attribute.attnum.
It certainly should be the case. starelid matches to pg_class.oid and
staattnum matches to pg_attribute.attnum.
My problem was that I was looking up event_date_time in pg_class.relname
(and finding it), but the oid matched nothing. when I looked for 'event' in
pg_class 'event_date_time' in
Nick Fankhauser [EMAIL PROTECTED] writes:
So... I updated stadistinct to the actual value of 92,000 as you suggested
and as you predicted, it did not change the plan a bit.
Just out of curiosity, what happens if you make it bigger than 92k?
Does a value 10x or 100x reality change the plan?
Just out of curiosity, what happens if you make it bigger than 92k?
Does a value 10x or 100x reality change the plan?
Neither one makes a change- perhaps something else is at work here- my
understanding of the finer points of query plans is shaky- Here is the query
and the plan I'm getting:
Nick Fankhauser [EMAIL PROTECTED] writes:
Neither one makes a change- perhaps something else is at work here- my
understanding of the finer points of query plans is shaky- Here is the query
and the plan I'm getting:
Well, one thing that's not a fine point is that if you are complaining
about