Re: Abnormal performance difference between Postgres and MySQL

On Mon, Feb 23, 2009 at 4:35 PM, Farhan Husain <russoue(at)gmail(dot)com> wrote:
>
>
> On Mon, Feb 23, 2009 at 5:27 PM, Gregory Stark <stark(at)enterprisedb(dot)com>
> wrote:
>>
>> Farhan Husain <russoue(at)gmail(dot)com> writes:
>>
>> > I can provide any other information needed and also the data if anyone
>> > wants.
>>
>> What did the query plans look like in both databases?
>>
>> In Postgres you can get the query plan with
>>
>> EXPLAIN ANALYZE select ...
>>
>> You can leave out the ANALYZE if you can't wait until the query completes
>> but
>> it will have much less information to diagnosis any problems.
>>
>> --
>> Gregory Stark
>> EnterpriseDB http://www.enterprisedb.com
>> Ask me about EnterpriseDB's Slony Replication support!
>
> I am doing the EXPLAIN ANALYZE now, it will take about 1 hour and 20 minutes
> again. I will get back to you once it is finished. Do you know how to get
> the query plan in MySQL?

Explain works in mysql. It just doesn't tell you a whole lot, because
the query planner's dumb as a brick. Note that often that stupid
query planner makes queries run really fast. When it doesn't, there's
not a lot of tuning you can do to fix it.

What does plain explain on pgsql tell you? Please attach output back
to the list from it for us to peruse.