Re: Very strange query difference between 7.3.6 and 7.4.6 - Mailing list pgsql-hackers

>>
>> On 7.4 and up you may have to set enable_hashagg = off to force a
>> Sort/GroupAggregate plan instead of HashAggregate.
>
O.k. on FC2 7.4.6 64bit I get:


-------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=80.00..82.50 rows=1000 width=404) (actual
time=235.064..235.068 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=404) (actual
time=0.024..10.409 rows=8845 loops=1)
 Total runtime: 236.703 ms
(3 rows)


With enable_hashagg on... With it enable_hashagg off I get:

GroupAggregate  (cost=69.83..134.83 rows=1000 width=404) (actual
time=688.150..688.151 rows=1 loops=1)
   ->  Sort  (cost=69.83..72.33 rows=1000 width=404) (actual
time=543.251..554.363 rows=8845 loops=1)
         Sort Key: post_id, topic_id, topic_title, topic_status,
topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id,
forum_name, forum_status, forum_id, auth_view, auth_read, auth_post,
auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce,
auth_pollcreate, auth_vote, auth_attachments
         ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=404)
(actual time=0.008..7.635 rows=8845 loops=1)
 Total runtime: 690.881 ms
(5 rows)

On the FC3 64bit, I am seeing similar results:

With enable_hashagg on:

        QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1041.15..1041.15 rows=1 width=333) (actual
time=260.543..260.544 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..510.45 rows=8845 width=333) (actual
time=11.638..68.744 rows=8845 loops=1)
 Total runtime: 261.195 ms
(3 rows)


With enable_hashagg off:

QUERY
PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1090.27..1643.08 rows=1 width=333) (actual
time=1075.690..1075.690 rows=1 loops=1)
   ->  Sort  (cost=1090.27..1112.38 rows=8845 width=333) (actual
time=943.242..946.261 rows=8845 loops=1)
         Sort Key: post_id, topic_id, topic_title, topic_status,
topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id,
forum_name, forum_status, forum_id, auth_view, auth_read, auth_post,
auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce,
auth_pollcreate, auth_vote, auth_attachments
         ->  Seq Scan on foo  (cost=0.00..510.45 rows=8845 width=333)
(actual time=0.044..15.936 rows=8845 loops=1)
 Total runtime: 1084.778 ms
(5 rows)


Odd that FC3 is so much slower, the FC3 machine puts the FC2 machine
to shame for IO.

However, The source query doesn't choose a hashagg on the FC3 machine,
which your
test case does. I am having problems getting 7.3.9 to start on the FC3
machine.
Very weird, I get this error:

IpcSemaphoreCreate: semget(key=5435117, num=17, 03600) failed: No space
left on device

Of which I am familiar with and know how to fix. However, I get the
error even with
default settings with the other instance of PostgreSQL (the 7.4.6)
shutdown. So I am
at a loss there.

O.k. I got 7.3.9 to operate as expected on FC2 (64bit) and these are my
results:

enable_hashagg on:

 HashAggregate  (cost=80.00..82.50 rows=1000 width=404) (actual
time=209.746..209.750 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=404) (actual
time=0.018..10.218 rows=8845 loops=1)
 Total runtime: 210.580 ms
(3 rows)

enable_hashagg off:

GroupAggregate  (cost=69.83..134.83 rows=1000 width=404) (actual
time=661.197..661.198 rows=1 loops=1)
   ->  Sort  (cost=69.83..72.33 rows=1000 width=404) (actual
time=517.531..528.360 rows=8845 loops=1)
         Sort Key: post_id, topic_id, topic_title, topic_status,
topic_replies, topic_time, topic_type, topic_vote, topic_last_post_id,
forum_name, forum_status, forum_id, auth_view, auth_read, auth_post,
auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce,
auth_pollcreate, auth_vote, auth_attachments
         ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=404)
(actual time=0.008..7.728 rows=8845 loops=1)
 Total runtime: 663.903 ms
(5 rows)


So at this point, from what I can tell FC3 64bit 7.4.6 is slower by an
at least 400ms (with the wrong plan) and is choosing the wrong plan. Yet
FC2 doesn't have these issues. Hmmm....

FC2 has glibc 2.3.3 and gcc 3.3.3
FC3 has glibc 2.3.4 and gcc 3.4.2

What next?

Sincerely,

Joshua D. Drake








>>
>>             regards, tom lane
>>
>>
>
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL