Roman,
> > Hey, Roman, how many records in BatchDetail, anyway?
>
> 23 million.
And MSSQL is returning results in 3 seconds? I find that a bit hard
to believe, unless this query is called repeatedly and that's the
figure for the last call, where the records are being cached. I'll
have to look at your hardware descriptions again.
> It seems to me that the big, big isolated problem is the index scan
> on batchdetail.tranamount.
Nope. This was a misimpression caused by batchdetail waiting for a
bunch of other processes to complete. Sometimes the parallelizing
gives me a wrong impression of what's holding up the query. Sorry if I
confused you.
> I hope we can come up with something soon.....it seems this index
> scan is a big part of the problem. I'm still really curious why the
> disk reads are so few with the index scan. Let's hope I can get it
> near the 3 second time for MSSQL by Friday!
Um, Roman, keep in mind this is a mailing list. I'm sure that
everyone here is happy to give you the tools to figure out how to fix
things, but only in a DIY fashion, and not on your schedule.
If you have a deadline, you'd better hire some paid query/database
tuning help. DB Tuning experts .... whether on MSSQL or Postgres ...
run about $250/hour last I checked.
-Josh Berkus