Actually I think I discovered part of the problem. My subquery did:
ORDER BY serial DESC LIMIT 1
where serial happened to be the primary key. The explain showed that it was doing an
index search on this serial. So I did the order by on a date field that doesn't have
an
index, so the query used a different index to do the query. The different index that
it
used was on the two keys that the mltable shares with the utable, so the query went
much
faster even though the explain estimated it taking longer. I do a vacuum analyze each
night in the cron.
More info: the utable is uniqe on two fields. The mltable has these coresponding key
fields in it, but there is more than one entry that has the same field combination. I
have an index on these because I knew I would want to get the entries by these two
fields.
The explain for the fast query shows:
-> Sort (cost=3106.17..3106.17 rows=1363 width=10)
-> Index Scan using mltabke_u_and_p_key on mltable ml (cost=0.00..3035.22
rows=1363 width=10)
While the explain for the slow one is:
-> Index Scan Backward using mltable_pkey on mltable ml (cost=0.00..28794.49
rows=1363
width=6)
I don't know why the planner thought the sort would be so expensive.
Stephan Szabo wrote:
> On Mon, 27 Aug 2001, Joseph Shraibman wrote:
>
>
>>Stephan Szabo wrote:
>>
>>>I think you'd want to move the entire query excepting the lastml where
>>>condition into a single subselect in the outer from with the lastml
>>>condition on the outside:
>>> select * from (<old query minus lastml condition>) as blah where
>>> lastml=2;
>>>
>>>However, I don't think this changes the computation that it's doing
>>>(simple example explains still show two subquery runs).
>>>
>>>
>>Yep, that works. I wasn't familiar with how the subselect in the from part works.
>But as
>>you say an explain still shows it doing the index scan twice.
>>
>> From the postgres docs:
>>
>> A sub-SELECT can appear in the FROM clause. This acts as though its output
>were
>>created as a temporary table for the duration of this single SELECT command. Note
>that the
>>sub-SELECT must be surrounded by parentheses, and an alias must be provided for it.
>>
>>So does postgres actually use a temporary table behind the scenses? It appears not.
>>
>
> I think what's happening is that the where condition is being pushed down
> into the subselect because usually that's the correct optimization, since
> you'd normally want
> select col1 from (select col1 from foo) as foo2 where col1=2;
> to do an index scan on foo.
>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
