> 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

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:

csitech=# 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, openeddatetime desc, callstatus desc,
calltype desc, callkey desc;
QUERY PLAN                                                
 Sort  (cost=60.01..60.05 rows=14 width=696) (actual
time=42393.56..43381.85 rows=510705 loops=1)
   Sort Key: aspid, openeddatetime, callstatus, calltype, callkey
   ->  Index Scan using call_idx13 on call  (cost=0.00..59.74 rows=14
width=696) (actual time=0.33..19679.01 rows=510705 loops=1)
         Index Cond: ((aspid = '123C'::bpchar) AND (openeddatetime >=
'2000-01-01 00:00:00-07'::timestamp with time zone) AND (openeddatetime
<= '2004-06-24 23:59:59.999-07'::timestamp with time zone))
 Total runtime: 43602.05 msec

FWIW, this is different hardware (Solaris 9/Sparc), but the same version
of Postgres (7.3.4).  The data is a superset of the data in the other
database (they are both snapshots taken from production).

I dropped and recreated the index on the other (Linux) machine, ran
vacuum analyse, then tried the query again.  It still performs a
sequence scan on the call table. :(

> Any chance you could put together a test case demonstrating the above
> behaviour? Everything from CREATE TABLE, through dataload to the

Forgive me for being thick: what exactly would be involved?  Due to
HIPAA regulations, I cannot "expose" any of the data.

I hesitated to bring this up because I wanted to focus on the technical
issues rather than have this degenerate into a religious war.  The chief
developer in charge of the project brought this query to my attention.
He has a fair amount of political sway in the company, and is now
lobbying to switch to MySQL because he maintains that PostgreSQL is
broken and/or too slow for our needs.  He has apparently benchmarked the
same query using MySQL and gotten much more favorable results (I have
been unable to corroborate this yet).



This message is intended for the sole use of the individual and entity to whom it is 
addressed, and may contain information that is privileged, confidential and exempt 
from disclosure under applicable law. If you are not the intended addressee, nor 
authorized to receive for the intended addressee, you are hereby notified that you may 
not use, copy, disclose or distribute to anyone the message or any information 
contained in the message. If you have received this message in error, please 
immediately advise the sender by reply email, and delete the message. Thank you.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to