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

Thanks Bruce!! Will set it off and retry.

On Sat, Apr 3, 2021 at 8:42 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
> > Hi Justin,
> > 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. Also, I suggest you review _all_ changes that have been made
> to the server since I am worried other unwise changes might also have
> been made.
>
> ---------------------------------------------------------------------------
>
> >
> > Regards,
> > Aditya.
> >
> > On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby <pryzby(at)telsasoft(dot)com>
> wrote:
> >
> > On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> > > so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123(at)gmail(dot)com>
> > napsal:
> > > > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> > > > time=174.318..198.539 rows=1 loops=1)"
> > > > " Workers Planned: 1"
> > > > " Workers Launched: 1"
> > > > " Single Copy: true"
> > > > " -> Index Scan using address1_i7 on address1 dom
> (cost=0.43..2.65 rows
> > =1
> > > > width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> > > > " Index Cond: (address_key = 6113763)"
> > > > "Planning Time: 0.221 ms"
> > > > "Execution Time: 198.601 ms"
> > >
> > > You should have broken configuration - there is not any reason to
> start
> > > parallelism - probably some option in postgresql.conf has very bad
> > value.
> > > Second - it's crazy to see 200 ms just on interprocess
> communication -
> > > maybe your CPU is overutilized.
> >
> > It seems like force_parallel_mode is set, which is for debugging and
> not
> > for
> > "forcing things to go faster". Maybe we should rename the
> parameter, like
> > parallel_mode_testing=on.
> >
> >
> http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
> >
> > --
> > Justin
> >
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>