Re: Abnormal performance difference between Postgres and MySQL

On Mon, Feb 23, 2009 at 4:35 PM, Farhan Husain  wrote:
>
>
> On Mon, Feb 23, 2009 at 5:27 PM, Gregory Stark 
> wrote:
>>
>> Farhan Husain  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.