Hi,
Paul:
Quite like Tom, I too think that its the first query that is more intriguing
than the second one. (The expected cost for the indexscan (A) query is 4x
the expected time for the 'Sequential Scan' (B) query !!)
Could you provide with the (complete output of) EXPLAIN ANALYZE times for
Robins [EMAIL PROTECTED] writes:
There is one thing though, that I couldn't really understand. Considering
that A's correlation in pg_stats being very high compared to B, isn't it 'a
better candidate' for a sequential scan as compared to B in this scenario ?
No, high correlation reduces the
Paul Smith wrote:
Why doesn't it use the other index? If use 'set enable_seqscan=0' then
it does.
Just a guess, but is the table clustered on column a? Maybe not
explicitly, but was it loaded from data that was sorted by a?
Analyzer calculates the correlation between physical order and each
Paul Smith [EMAIL PROTECTED] writes:
If I do
EXPLAIN SELECT * FROM x ORDER BY a;
it says
Index Scan using y on x (cost=0.00..2903824.15 rows=1508057 width=152)
That's what I'd expect
However, if I do
EXPLAIN SELECT * FROM x ORDER BY b;
it says
Sort (cost=711557.34..715327.48
At 16:26 04/05/2007, you wrote:
Paul Smith wrote:
Why doesn't it use the other index? If use 'set enable_seqscan=0'
then it does.
Just a guess, but is the table clustered on column a? Maybe not
explicitly, but was it loaded from data that was sorted by a?
I wouldn't have thought so - a is