Re: Why isn't an index scan being used?
On Tue, Feb 19, 2019 at 09:29:46PM -0700, Michael Lewis wrote: > On Tue, Feb 19, 2019, 8:00 PM Andrew Gierth > wrote: > > > > "Abi" == Abi Noda writes: > > Abi> However, when I index the closed column, a bitmap scan is used > > Abi> instead of an index scan, with slightly slower performance. Why > > Abi> isn't an index scan being used, given that the exact same number > > Abi> of rows are at play as in my query on the state column? > > > > Most likely difference is the correlation estimate for the conditions. > > The cost of an index scan includes a factor based on how well correlated > > the physical position of rows is with the index order, because this > > affects the number of random seeks in the scan. But for nulls this > > estimate cannot be performed, and bitmapscan is cheaper than plain > > indexscan on poorly correlated data. > > Does this imply that the optimizer would always prefer the bitmapscan > rather than index scan even if random page cost = 1, aka sequential cost, > when the correlation is unknown like a null? Or only when it thinks random > access is more expensive by some significant factor? No; for one, since for a bitmap scan, the heap scan can't begin until the index scan is done, so there's a high(er) initial cost. Otherwise bitmap scan could always be used and all access could be ordered (even if not sequential). Justin
Re: Why isn't an index scan being used?
On Tue, Feb 19, 2019 at 11:59 PM Abi Noda wrote: > Thanks Justin. > > The 4ms different in the examples isn't an accurate benchmark. I'm seeing > about a ~20% difference over a larger sample size. And this is on a fork of > the production database. > Please show the execution plans from that larger sample, if that is the one that is most relevant. You can "set enable_bitmapscan = off" to get rid of the bitmap scan in order to see the estimated cost and actual performance of the next-best plan (which will probably the regular index scan). Cheers, Jeff
Re: How can sort performance be so different
On Mon, Feb 18, 2019 at 9:49 AM Bob Jolliffe wrote: > > Sorry Merlin for not replying earlier. The difference is indeed hard > to understand but it is certainly there. We altered the collation to > use on the name field in that table and the problem has gone. Having > having solved the immediate problem we haven't investigated much > further yet. > > Not sure what exactly you mean by "other conversions"? I hand tested similar query for other (generally western) collations. Did not observe anything nearly so bad. What I'm hoping is that this is some kind of weird performance issue specific to your installation; in the worst (unfortunately likely) case we are looking at something specific to your specific sort collation :(. merlin
Re: How can sort performance be so different
On Wed, 20 Feb 2019 at 21:35, Merlin Moncure wrote: > > On Mon, Feb 18, 2019 at 9:49 AM Bob Jolliffe wrote: > > > > Sorry Merlin for not replying earlier. The difference is indeed hard > > to understand but it is certainly there. We altered the collation to > > use on the name field in that table and the problem has gone. Having > > having solved the immediate problem we haven't investigated much > > further yet. > > > > Not sure what exactly you mean by "other conversions"? > > > I hand tested similar query for other (generally western) collations. > Did not observe anything nearly so bad. What I'm hoping is that this > is some kind of weird performance issue specific to your installation; > in the worst (unfortunately likely) case we are looking at something > specific to your specific sort collation :(. > It seems not to be (completely) particular to the installation. Testing on different platforms we found variable speed difference between 100x and 1000x slower, but always a considerable order of magnitiude. The very slow performance comes from sorting Lao characters using en_US.UTF-8 collation.
Re: How can sort performance be so different
On Wed, Feb 20, 2019 at 1:42 PM Bob Jolliffe wrote: > It seems not to be (completely) particular to the installation. > Testing on different platforms we found variable speed difference > between 100x and 1000x slower, but always a considerable order of > magnitiude. The very slow performance comes from sorting Lao > characters using en_US.UTF-8 collation. I knew that some collations were slower, generally for reasons that make some sense. For example, I was aware that ICU's use of Japanese standard JIS X 4061 is particularly complicated and expensive, but produces the most useful possible result from the point of view of a Japanese speaker. Apparently glibc does not use that algorithm, and so offers less useful sort order (though it may actually be faster in that particular case). I suspect that the reasons why the Lao locale sorts so much slower may also have something to do with the intrinsic cost of supporting more complicated rules. However, it's such a ridiculously large difference that it also seems likely that somebody was disinclined to go to the effort of optimizing it. The ICU people found that to be a tractable goal, but they may have had to work at it. I also have a vague notion that there are special cases that are more or less only useful for sorting French. These complicate the implementation of UCA style algorithms. I am only speculating, based on what I've heard about other cases -- perhaps this explanation is totally wrong. I know a lot more about this stuff than most people on this mailing list, but I'm still far from being an expert. -- Peter Geoghegan
neither CPU nor IO bound, but throttled performance
Hi, I have an Amazon Linux based Postgresql 11 server here on a t2.medium EC2 instance. It is serving 24 worker processes that read jobs from a queue (thanks to SELECT ... FOR UPDATE SKIP LOCKED!) and do jobs some of which are reading and writing business data to the database, others are only reading, and some don't hit the business data at all, only the queue. Everything flows quite nicely. Except, I don't understand why I can't max out the CPU or the IO, instead, IO is almost negligible yet the CPU is at 30% hardly hitting 50%. Here I give you a view of top: top - 23:17:09 up 45 days, 2:07, 4 users, load average: 20.32, 18.92, 13.80 Tasks: 338 total, 24 running, 111 sleeping, 0 stopped, 0 zombie %Cpu(s): 28.7 us, 2.5 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 68.7 st KiB Mem : 4040028 total, 1070368 free, 324460 used, 2645200 buff/cache KiB Swap: 0 total, 0 free, 0 used. 2223720 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7678 postgres 20 0 1235072 509744 506356 R 8.7 12.6 1:14.82 postgres: auser integrator 172.31.61.242(25783) BIND 7998 postgres 20 0 1235108 516480 512772 R 8.7 12.8 1:16.20 postgres: auser integrator 172.31.49.159(51708) SELECT 2183 postgres 20 0 1261436 985.8m 982544 R 8.5 25.0 0:44.04 postgres: auser integrator [local] SELECT 7653 postgres 20 0 1235180 501760 497984 R 8.2 12.4 1:13.66 postgres: auser integrator 172.31.54.158(47959) SELECT 7677 postgres 20 0 1235144 506740 502980 S 8.2 12.5 1:13.54 postgres: auser integrator 172.31.61.242(56510) idle in t+ 7680 postgres 20 0 1234684 484356 481100 R 8.2 12.0 1:13.86 postgres: auser integrator 172.31.61.242(49966) SELECT 2631 postgres 20 0 1235120 937964 934528 R 7.9 23.2 10:48.39 postgres: auser integrator 172.31.49.159(33522) idle in t+ 7664 postgres 20 0 1235104 524664 520976 R 7.9 13.0 1:13.95 postgres: auser integrator 172.31.57.147(30036) BIND 7682 postgres 20 0 1234660 496188 492956 R 7.9 12.3 1:15.50 postgres: auser integrator 172.31.61.242(26330) COMMIT 7687 postgres 20 0 1234876 490104 486656 R 7.9 12.1 1:16.77 postgres: auser integrator 172.31.63.71(25285) BIND 7660 postgres 20 0 1235100 502004 498596 R 7.6 12.4 1:18.00 postgres: auser integrator 172.31.57.147(46051) PARSE 7662 postgres 20 0 1235148 503532 500280 R 7.6 12.5 1:14.03 postgres: auser integrator 172.31.57.147(48852) UPDATE 7681 postgres 20 0 1234688 516864 513596 R 7.6 12.8 1:17.77 postgres: auser integrator 172.31.61.242(48192) SELECT 7685 postgres 20 0 1235096 515352 511968 R 7.6 12.8 1:16.17 postgres: auser integrator 172.31.63.71(62540) BIND 7689 postgres 20 0 1235100 509504 505836 S 7.6 12.6 1:14.78 postgres: auser integrator 172.31.63.71(12287) idle in tr+ 7684 postgres 20 0 1235052 500336 496916 R 7.3 12.4 1:14.83 postgres: auser integrator 172.31.63.71(19633) BIND 7654 postgres 20 0 1235224 514512 511040 S 7.0 12.7 1:18.89 postgres: auser integrator 172.31.57.147(43437) idle in t+ 7656 postgres 20 0 1234684 510900 507636 R 7.0 12.6 1:16.19 postgres: auser integrator 172.31.54.158(30397) idle in t+ 7661 postgres 20 0 1234684 514920 511648 S 7.0 12.7 1:16.27 postgres: auser integrator 172.31.57.147(38243) SELECT 7679 postgres 20 0 1235112 512228 508544 R 7.0 12.7 1:14.60 postgres: auser integrator 172.31.61.242(34261) PARSE 7663 postgres 20 0 1234684 517068 513812 R 6.8 12.8 1:17.42 postgres: auser integrator 172.31.57.147(19711) SELECT 7655 postgres 20 0 1235036 505584 502208 R 6.5 12.5 1:16.17 postgres: auser integrator 172.31.54.158(24401) BIND 7658 postgres 20 0 1235072 509432 506108 R 6.5 12.6 1:15.90 postgres: auser integrator 172.31.54.158(48566) idle in t+ 7659 postgres 20 0 1234688 497488 494232 S 6.2 12.3 1:15.70 postgres: auser integrator 172.31.54.158(49841) idle in t+ 7686 postgres 20 0 1234748 508412 505084 R 5.9 12.6 1:14.70 postgres: auser integrator 172.31.63.71(54938) BIND 7688 postgres 20 0 1234708 502416 499204 S 5.9 12.4 1:14.14 postgres: auser integrator 172.31.63.71(49857) SELECT 7657 postgres 20 0 1234684 513740 510476 R 5.6 12.7 1:15.96 postgres: auser integrator 172.31.54.158(47300) SELECT 1304 ec2-user 20 0 171400 4648 3840 R 1.7 0.1 1:25.66 top -c 8492 root 20 0 0 0 0 R 1.7 0.0 0:00.41 [kworker/1:2] and of iostat: avg-cpu: %user %nice %system %iowait %steal %idle 36.41 0.00 3.80 0.00 59.78 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvda 0.00 0.00 0.00 7.69 0.00 92.31 24.00 0.02 3.00 0.00 3.00 3.00 2.31 xvdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvdg 0.00 0.00 0.00 11.54 0.00 130.77 22.67 0.05 4.00 0.00 4.00 0.33 0.38 xvdh 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvdi 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvdj 0.00 0.00 5.77 0.96 46.15 7.69 16.00 0.01 1.14 0.67 4.00 1.14 0.77 xvdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvdl 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Re: How can sort performance be so different
On Wed, Feb 20, 2019 at 2:25 PM Peter Geoghegan wrote: > I suspect that the reasons why the Lao locale sorts so much slower may > also have something to do with the intrinsic cost of supporting more > complicated rules. I strongly suspect that it has something to do with the issue described here specifically: http://userguide.icu-project.org/collation/concepts#TOC-Thai-Lao-reordering -- Peter Geoghegan
Re: neither CPU nor IO bound, but throttled performance
On Wed, Feb 20, 2019 at 06:32:49PM -0500, Gunther wrote: > Hi, I have an Amazon Linux based Postgresql 11 server here on a t2.medium > EC2 instance. > > Everything flows quite nicely. Except, I don't understand why I can't max > out the CPU or the IO, instead, IO is almost negligible yet the CPU is at > 30% hardly hitting 50%. > avg-cpu: %user %nice %system %iowait %steal %idle 36.41 0.00 3.80 0.00 59.78 > 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await > r_await w_await svctm %util xvda 0.00 0.00 0.00 7.69 0.00 92.31 24.00 0.02 This is unreadable, please try to attach it ? > previously I had hit 100 %util here, that was when I didn't have the tables > so spread out over so many tablespaces. Now I have it spread out like in the > olden days where you spread out your tables over many "spindles", and I did > this here so I could see which tables or indexes would be bottlenecks. What was the old storage configuration and what is it now ? > So how can it be that queries take quite long without the process running at > higher CPU%? You said everything flows nicely, but take a long time, and "throttled", can you show an high-level performance change ? Other than %cpu or %io. Justin
