Re: Query Performance SQL Server vs. Postgresql

Список
Период
Сортировка
От Humair Mohammed
Тема Re: Query Performance SQL Server vs. Postgresql
Дата
Msg-id COL115-W65B2FE2559E1E7ACCDB64EA83E0@phx.gbl
обсуждение исходный текст
Ответ на Re: Query Performance SQL Server vs. Postgresql  ()
Ответы Re: Query Performance SQL Server vs. Postgresql  (Merlin Moncure)
Список pgsql-performance
Дерево обсуждения
 (Humair Mohammed, )
 Re:  (Jayadevan M, )
 Re:  (Mark Kirkwood, )
 Re:  (Pavel Stehule, )
  Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
 Re: Query Performance SQL Server vs. Postgresql  (Pavel Stehule, )
  Re: Query Performance SQL Server vs. Postgresql  (Tomas Vondra, )
   Re: Query Performance SQL Server vs. Postgresql  (Rich, )
  Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
   Re: Query Performance SQL Server vs. Postgresql  (Tom Lane, )
    Re: Query Performance SQL Server vs. Postgresql  ("Kevin Grittner", )
   Re: Query Performance SQL Server vs. Postgresql  (Pavel Stehule, )
    Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
     Re: Query Performance SQL Server vs. Postgresql  (, )
      Re: Query Performance SQL Server vs. Postgresql  (Tom Lane, )
       Re: Query Performance SQL Server vs. Postgresql  (Robert Haas, )
        Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
         Re: Query Performance SQL Server vs. Postgresql  (, )
          Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
           Re: Query Performance SQL Server vs. Postgresql  (Merlin Moncure, )
         Re: Query Performance SQL Server vs. Postgresql  (Samuel Gendler, )
          Re: Query Performance SQL Server vs. Postgresql  (, )
    Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
     Re: Query Performance SQL Server vs. Postgresql  (Pavel Stehule, )
      Re: Query Performance SQL Server vs. Postgresql  (Humair Mohammed, )
     Re: Query Performance SQL Server vs. Postgresql  (, )
      Re: Query Performance SQL Server vs. Postgresql  (, )
 Re: Query Performance SQL Server vs. Postgresql  (Pavel Stehule, )
 Re: Query Performance SQL Server vs. Postgresql  (Pavel Stehule, )
I did some further analysis and here are the results:

work_mem;response_time
1MB;62 seconds
2MB;2 seconds
4MB;700 milliseconds
8MB;550 milliseconds

In all cases shared_buffers were set to the default value of 32MB. As you can see the 1 to 2 MB jump on the work_mem does wonders. I probably don't need this to be any higher than 8 or 16 MB. Thanks to all for help!

Humair

> Date: Mon, 22 Nov 2010 12:00:15 +0100
> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
> From:
> To:
> CC:
>
> >
> >
> > Correct, the optimizer did not take the settings with the pg_ctl reload
> > command. I did a pg_ctl restart and work_mem now displays the updated
> > value. I had to bump up all the way to 2047 MB to get the response below
> > (with work_mem at 1024 MB I see 7 seconds response time) and with 2047 MB
> > (which is the max value that can be set for work_mem - anything more than
> > that results in a FATAL error because of the limit) the results are below.
>
> Hm, can you post explain plan for the case work_mem=1024MB. I guess the
> difference is due to caching. According to the explain analyze, there are
> just cache hits, no reads.
>
> Anyway the hash join uses only about 40MB of memory, so 1024MB should be
> perfectly fine and the explain plan should be exactly the same as with
> work_mem=2047MB. And the row estimates seem quite precise, so I don't
> think there's some severe overestimation.
>
> Tomas
>

В списке pgsql-performance по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: Query Performance SQL Server vs. Postgresql
Следующее
От: Craig Ringer
Дата:
Сообщение: Re: Performance under contention