On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote: > > The query plan is all garbled by mail , could you resend? Or post a link from > > https://explain.depesz.com/ On Sun, Jan 14, 2018 at 06:36:02PM -0800, Neto pr wrote: > I was not able to upload to the site, because I'm saving the execution > plan in the database, and when I retrieve it, it loses the line breaks, That's why it's an issue for me, too.. > > What OS/kernel are you using? LVM? filesystem? I/O scheduler? partitions? > > See below the Disk FileSystem -------------------------------- > root@hp2ml110deb:/# fdisk -l > Disk /dev/sda: 931.5 GiB, 1000204886016 bytes, 1953525168 sectors > > Disk /dev/sdb: 465.8 GiB, 500107862016 bytes, 976773168 sectors > Units: sectors of 1 * 512 = 512 bytes > Sector size (logical/physical): 512 bytes / 512 bytes > I/O size (minimum/optimal): 512 bytes / 512 bytes > ---------------------------------------------------------------------------- What about sdb partitions/FS? On Sun, Jan 14, 2018 at 06:25:40PM -0800, Neto pr wrote: > The DBMS and tablespace of users is installed in /dev/sdb SSD. Is that also a temp_tablespace ? Or are your hashes spilling to HDD instead ? Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) Buffers: shared hit=3773802 read=7120852, temp read=3550293 written=3541542 Are your SSD being used for anything else ? What about these? > > readahead? blockdev --getra > > If you're running under linux, maybe you can just send the output of: > > for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done > > or: tail /sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size} > > Can you reproduce the speed difference using dd ? > > time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size > > > > Or: bonnie++ -f -n0 Justin
