Considering you're pulling out 450k rows in 8 seconds, I'd also guess
the data is mostly in memory. Is that normal? Or is this a result of
having run several test queries against the same data multiple times?
Ah yes, that would have been the result of running the query several
times...
Oddly enough, I put the same database on a different machine, and the
query now behaves as I hoped all along. Notice that I'm using the
real query, with the aspid in asc and the other fields in desc order,
yet the query does use the call_idx13 index:
Notice that while it only takes 19
However, this query performs a sequence scan on the table, ignoring
the
call_idx13 index (the only difference is the addition of the aspid
field
in the order by clause):
You do not have an index which matches the ORDER BY, so PostgreSQL
cannot simply scan the index for the data you
OK, that makes sense; however, this doesn't:
elon2=# explain analyse select * from call where aspid='123C' and
OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24
23:59:59.999' order by aspid asc, openeddatetime asc, callstatus asc,
calltype asc, callkey asc;
I've modified the
The limit is tricking you.
I guess a sequential scan is cheaper than an index scan with the limit 26 found there.
I am wrong?
Greets
--
---
Guido Barosio
Buenos Aires, Argentina
---
However, this query performs a sequence scan on the table, ignoring the
call_idx13 index (the only difference is the addition of the aspid field
in the order by clause):
You do not have an index which matches the ORDER BY, so PostgreSQL
cannot simply scan the index for the data you want. Thus
Can someone explain what I'm missing here? This query does what I
expect--it uses the foo index on the openeddatetime, callstatus,
calltype, callkey fields:
elon2=# explain analyse select * from call where aspid='123C' and
OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24
]
Sent: Wednesday, July 07, 2004 2:46 PM
To: Joel McGraw
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] query plan wierdness?
The limit is tricking you.
I guess a sequential scan is cheaper than an index scan with the limit
26 found there.
I am wrong?
Greets
On Wed, 7 Jul 2004, Joel McGraw wrote:
However, this query performs a sequence scan on the table, ignoring the
call_idx13 index (the only difference is the addition of the aspid field
in the order by clause):
elon2=# explain analyse select * from call where aspid='123C' and
OpenedDateTime