Re: Same query taking less time in low configuration machine - Mailing list pgsql-general

From Vishwa Kalyankar
Subject Re: Same query taking less time in low configuration machine
Date
Msg-id CAFWaVn0v6Z41Xkp5PQkcz8Ge2rmQCgkDp7xhkw2EhcPvyoxErA@mail.gmail.com
Whole thread Raw
In response to Re: Same query taking less time in low configuration machine  (Philip Semanchuk <philip@americanefficient.com>)
List pgsql-general
HI,

OS cache is updated and I had run the query few times with almost the same result each time.

Regards,
Vishwa

On Tue, Jul 14, 2020 at 6:16 PM Philip Semanchuk <philip@americanefficient.com> wrote:


> On Jul 14, 2020, at 5:27 AM, Vishwa Kalyankar <vishwakalyankar8@gmail.com> wrote:
>
> Hi, 
>
> I have two machines - one with 8GB RAM & 4core CPU and the other with 64GB Ram & 24 core CPU.  Both machines have the same DB (Postgres 12 + Postgis 2.5.3).  Same query is taking less time in low end machine whereas more time in high end machine.  Any thoughts on where to look?  I have tuned the db in both machines according to https://pgtune.leopard.in.ua/#/
>
>
> Below I am pasting the output of query explain in both the machines.
>
>  -bash-4.2$ psql -p 5434
> psql (12.3)
> Type "help" for help.
>
> postgres=# \c IPDS_KSEB;
> You are now connected to database "IPDS_KSEB" as user "postgres".
> IPDS_KSEB=# explain analyze select * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ;
>                                                                        QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Function Scan on kseb_geometry_trace_with_barrier_partition  (cost=0.25..10.25 rows=1000 width=169) (actual time=11626.548..11626.568 rows=254 loops=1)
>  Planning Time: 0.212 ms
>  Execution Time: 11628.590 ms
>
>
> -bash-4.2$ psql -p 5422
> psql (12.3)
> Type "help" for help.
>
> postgres=# \c IPDS_KSEB;
> You are now connected to database "IPDS_KSEB" as user "postgres".
> IPDS_KSEB=# explain analyze select * from kseb_geometry_trace_with_barrier_partition(5,'kottarakara_version',437,'htline',2) ;
>                                                                        QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Function Scan on kseb_geometry_trace_with_barrier_partition  (cost=0.25..10.25 rows=1000 width=169) (actual time=22304.425..22304.448 rows=254 loops=1)
>  Planning Time: 0.219 ms
>  Execution Time: 22352.219 ms
> (3 rows)
>

Hi Vishwa,
Is it possible that your data is in the cache on the low end machine but not on the high end machine? There’s both the Postgres cache and the OS disk cache to consider. You can see what’s in the Postgres cache with an extension like pg_buffercache. I don’t know of a way to see what’s in the OS cache; maybe others do.

Cheers
Philip






pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Surprising connection issue
Next
From: Marc Millas
Date:
Subject: Re: some random() clarification needed