On 5/26/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Merlin Moncure" <mmoncure@xxxxxxxxx> writes:
> did you have a key on a,b,c?
Yeah, I did
create index t1i on t1 (a,b,c);
Do I need to use some other syntax to get it to work?
can't thing of anything, I'm running completely stock, did you do a
optimize table foo? is the wind blowing in the right direction?
> select count(*) from (select a,b,max(c) group by a,b) q;
> blows the high performance case as does putting the query in a view.
I noticed that too, while trying to suppress the returning of the
results for timing purposes ... still a few bugs in their optimizer
obviously. (Curiously, EXPLAIN still claims that the index is being
used.)
well, they do some tricky things pg can't do for architectural reasons
but the special case is obviously hard to get right. I suppose this
kinda agrues against doing all kinds of acrobatics to optimize mvcc
weak cases like the above and count(*)...better to make heap access as
quick as possible.
> mysql> select user_id, acc_id, max(sample_date) from usage_samples group by 1,2
> 939 rows in set (0.07 sec)
> mysql> select user_id, acc_id, max(sample_date) from usage_samples group by 1,2
> 939 rows in set (1.39 sec)
oops, pasted the wrong query..case 2 should have been
select user_id, acc_id, max(sample_date), disksize from usage_samples
group by 1,2
illustrating what going to the heap does to the time.
merlin
