Re: query plan wierdness?

>
> 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:


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
EXPLAIN
> ANALYZE.


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

<background>
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).
</background>


-Joel

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.