On Thu, 16 Jul 2020 at 09:50, Thomas Munro <thomas.munro@gmail.com> wrote:
> I'd look at EXPLAIN (BUFFERS) or auto_explain.log_buffers to get more
> information on cache hits and misses. I'd look for settings
> differences with EXPLAIN (SETTINGS) to see if there's anything
> accidentally set differently (maybe JIT or paralelism or something
> like that). I'd look at pg_stat_activity repeatedly while it runs to
> see what the processes are doing, especially the wait_event column.
> I'd also look at the CPU and I/O on the systems with operating system
> tools like iostat, htop, perf to try to find the difference.
It might also be good to look at size of the tables and indexes that
are looked at within the function. If the smaller end machine was
loaded with data via pg_restore form a pg_dump taken from the larger
machine then the indexes might be in much better shape and the heap
may have less bloat. Of course, that's just speculation. We've not
seen what the function does yet.
Vishwa, it would be good if you could follow the guide here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions , in particular:
"Post the definitions of all tables and indexes referenced in the
query. If the query touches views or custom functions, we'll need
those definitions as well. Run psql command "\d table" with the
tables/views/indices referenced in the problem query."
David