Re: Query is slower with a large proportion of NULLs in several columns
On Tue, Dec 21, 2021 at 12:33:06AM -0500, Tom Lane wrote: > So now we have a real mystery about what is happening on Lars' > system. Those numbers can't be right. I realized Lars said it was x86_64/Linux, but I'm hoping to hear back with more details: What OS version? Is it a VM of some type ? How did you install postgres? From a package or compiled from source? grep -r HAVE_CLOCK_GETTIME /usr/pgsql-13/include Send the exact command and output you used to run the query? Why does your explain output have IO timing but not Buffers: hit/read ? -- Justin
Re: Query is slower with a large proportion of NULLs in several columns
Justin, Thanks for your continued interest. I'm running PostgreSQL under AWS Aurora, and I didn't set it up or install it, so I'm not sure about the OS version. I can't run the grep command since I don't know how to get down to the command line on the actual box running Aurora. I just connect to PostgreSQL from either my desktop or an EC2 Linux instance. SQL I entered was: set max_parallel_workers_per_gather = 0; explain (analyze, buffers, settings) select roys_creation_user, count(*) from eu.royalty_with_null group by roys_creation_user; The output is shown earlier in this thread. I have no idea why I have IO timings but not buffers hit/read. On Tue, Dec 21, 2021 at 1:13 PM Justin Pryzby wrote: > On Tue, Dec 21, 2021 at 12:33:06AM -0500, Tom Lane wrote: > > So now we have a real mystery about what is happening on Lars' > > system. Those numbers can't be right. > > I realized Lars said it was x86_64/Linux, but I'm hoping to hear back with > more > details: > > What OS version? > Is it a VM of some type ? > How did you install postgres? From a package or compiled from source? > grep -r HAVE_CLOCK_GETTIME /usr/pgsql-13/include > Send the exact command and output you used to run the query? > Why does your explain output have IO timing but not Buffers: hit/read ? > > -- > Justin >
Re: Query is slower with a large proportion of NULLs in several columns
Lars Bergeson writes: > I'm running PostgreSQL under AWS Aurora, and I didn't set it up or install > it, so I'm not sure about the OS version. Oh! Aurora is not Postgres. My admittedly-not-well-informed understanding is that they stuck a Postgres front end on their existing storage engine, so it's not surprising if storage-level behaviors are quite different from stock Postgres. regards, tom lane
Re: Query is slower with a large proportion of NULLs in several columns
On Tue, Dec 21, 2021 at 4:07 PM Tom Lane wrote: > Lars Bergeson writes: > > I'm running PostgreSQL under AWS Aurora, and I didn't set it up or > install > > it, so I'm not sure about the OS version. > > Oh! Aurora is not Postgres. My admittedly-not-well-informed > understanding is that they stuck a Postgres front end on their > existing storage engine, so it's not surprising if storage-level > behaviors are quite different from stock Postgres. > > I do wish Amazon would be more considerate and modify what version() outputs to include "AWS Aurora" somewhere in the human readable string. Though the lack really isn't an excuse for reports of this nature to omit such a crucial hardware/hosting detail. The rest of the problem statement, even with the "newbie to PostgreSQL" qualifier, was written well enough I hadn't really considered that it would be anything but stock PostgreSQL on a personal VM setup for testing. David J.
