Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

so 3. 4. 2021 v 19:45 odesílatel aditya desai <admad123(at)gmail(dot)com> napsal:

> Yes. I have made suggestions on connection pooling as well. Currently it
> is being done from Application side.
>

It is usual - but the application side pooling doesn't solve well
overloading. The behaviour of the database is not linear. Usually opened
connections are not active. But any non active connection can be changed to
an active connection (there is not any limit for active connections), and
then the performance can be very very slow. Good pooling and good setting
of max_connections is protection against overloading. max_connection should
be 10-20 x CPU cores (for OLTP)

Regards

Pavel

> On Sat, Apr 3, 2021 at 11:12 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> so 3. 4. 2021 v 19:37 odesílatel aditya desai <admad123(at)gmail(dot)com>
>> napsal:
>>
>>> Hi Justin/Bruce/Pavel,
>>> Thanks for your inputs. After setting force_parallel_mode=off Execution
>>> time of same query was reduced to 1ms from 200 ms. Worked like a charm. We
>>> also increased work_mem to 80=MB. Thanks
>>>
>>
>> super.
>>
>> The too big max_connection can cause a lot of problems. You should
>> install and use pgbouncer or pgpool II.
>>
>>
>> https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>> again.
>>>
>>> Regards,
>>> Aditya.
>>>
>>> On Sat, Apr 3, 2021 at 9:14 PM aditya desai <admad123(at)gmail(dot)com> wrote:
>>>
>>>> Thanks Justin. Will review all parameters and get back to you.
>>>>
>>>> On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby <pryzby(at)telsasoft(dot)com>
>>>> wrote:
>>>>
>>>>> On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote:
>>>>> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>>>> > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
>>>>> > >> Yes, force_parallel_mode is on. Should we set it off?
>>>>> >
>>>>> > > Yes. I bet someone set it without reading our docs:
>>>>> >
>>>>> > >
>>>>> https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
>>>>> >
>>>>> > > --> Allows the use of parallel queries for testing purposes even
>>>>> in cases
>>>>> > > --> where no performance benefit is expected.
>>>>> >
>>>>> > > We might need to clarify this sentence to be clearer it is _only_
>>>>> for
>>>>> > > testing.
>>>>> >
>>>>> > I wonder why it is listed under planner options at all, and not under
>>>>> > developer options.
>>>>>
>>>>> Because it's there to help DBAs catch errors in functions incorrectly
>>>>> marked as
>>>>> parallel safe.
>>>>>
>>>>> --
>>>>> Justin
>>>>>
>>>>