From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
Cc: | Joe Conway <mail(at)joeconway(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: The standard 'why does it take so long' question |
Date: | 2002-08-09 13:41:16 |
Message-ID: | 354.1028900476@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> I really don't know sufficient to be able to see why the stats would
> favour one index over the other. Although looking at the pg_stats
> entries below now I notice that the correlation for the time column is
> 1, compared to 0.058 for the poster_id.
Ah, of course, that would do it. Thinking about it, I see that the
system is not really very bright about ordering considerations for
multicolumn indexes. On a macro scale, the posterid/time index is
poorly correlated with the physical table order --- but when you
consider only the set of entries for a single posterid over a small
time range, the index is pretty well correlated. The planner doesn't
consider that effect, so it mistakenly credits the time/posterid
index with much higher correlation and hence lower scan cost than the
other.
I already had a todo item to reconsider the costing estimates for
multicolumn indexes --- will see if we can be smarter about cases like
this.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-08-09 14:07:27 | Re: UNIQUE constraint matching given keys for referenced |
Previous Message | Jeff Eckermann | 2002-08-09 13:31:01 | Re: UNIQUE constraint matching given keys for referenced |