> 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 seconds to pull the data out of the
table, it is spending 30 seconds sorting it -- so the index scan isn't
buying you very much.
Try it again with ORDER BY ascid DESC and you should get the query down
to 20 seconds in total on that Sparc; so I wouldn't call it exactly what
you wanted.
he decision about whether to use an index or not, is borderline. And as
you've shown they take approximately the same amount of time. Use of an
index will not necessarily be faster than a sequential scan -- but the
penalty for accidentally selecting one when it shouldn't have is much
higher.
> > 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.
Of course. But that doesn't mean you couldn't create table different
name and muck around with the values. But you're getting what you want,
so it isn't a problem anymore.
> <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>
I wouldn't be surprised if MySQL did run this single query faster with
nothing else going on during that time. MySQL was designed primarily
with a single user in mind, but it is unlikely this will be your
production situation so the benchmark is next to useless.
Connect 50 clients to the databases running this (and a mixture of other
selects) while another 20 clients are firing off updates, inserts,
deletes on these and other structures -- or whatever matches your full
production load.
This is what PostgreSQL (and a number of other DBs) are designed for,
typical production loads.