On Thu, 16 Jul 2020 at 09:50, Thomas Munro <thomas.munro@xxxxxxxxx> 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
