Hi,
We recently had an issue in production, where a bitmap scan was chosen
instead of an index scan. Despite being 30x slower, the bitmap scan had
about the same cost as the index scan.
I've found some cases where similar issues with bitmap scans were
reported before:
https://www.postgresq
On 01/12/2017 20:34, Justin Pryzby wrote:
On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote:
We recently had an issue in production, where a bitmap scan was chosen
instead of an index scan. Despite being 30x slower, the bitmap scan had
about the same cost as the index scan
On 02/12/2017 01:11, Justin Pryzby wrote:
I tried to reproduce this issue and couldn't, under PG95 and 10.1:
On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote:
On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote:
We recently had an issue in production, wh
On 02/12/2017 07:51, Jeff Janes wrote:
On Fri, Dec 1, 2017 at 3:54 PM, Vitaliy Garnashevich
mailto:vgarnashev...@gmail.com>> wrote:
On 02/12/2017 01:11, Justin Pryzby wrote:
I tried to reproduce this issue and couldn't, under PG95 and 10.1:
On Fri, Dec 01, 20
On 02/12/2017 23:17, Jeff Janes wrote:
Right, so there is a cpu costing problem (which could only be fixed by
hacking postgresql and recompiling it), but it is much smaller of a
problem than the IO cost not being accurate due to the high hit rate.
Fixing the CPU costing problem is unlikely to m
On 03/12/2017 01:44, Tom Lane wrote:
I think it'd be a serious error to screw around with your cost settings
on the basis of a single case in which the rowcount estimates are so
far off. It's really those estimates that are the problem AFAICS.
The core issue in this example is that, the way the
On 03/12/2017 03:27, Jeff Janes wrote:
Due to that, when I disable bitmapscans and seqscans, I start getting
slow index scans on the wrong index, i2 rather than i1. I don't know
why he doesn't see that in his example.
When I increase effective_cache_size to 1024MB, I start getting the plan
wit
What seems odd to me is that in different kinds of tests (with
different frequency of column values):
i1 Rows Removed by Filter = 900156, 179792, 89762 (decreased a lot)
i1 buffers = 46983, 44373, 39928 (decreased, but not a lot)
i1 best case time = 756.045, 127.814, 79.492 (decreased a lot, a
Hi,
The following talk describes an issue with how Linux may handle memory
allocation for Postgres. The issue may cause many hundreds of megabytes
not being released in some cases.
PostgreSQL and RAM usage [Feb 27, 2017]
https://www.youtube.com/watch?v=EgQCxERi35A
see between minutes 33 and 3
Hi,
I've tried to run a benchmark, similar to this one:
https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com
CREATE TABLESPACE test OWNER postgres LOCATION '/path/
her than AWS. (We moved to Google Cloud and
have been very happy there. The performance is much more consistent,
the management UI is more intuitive, AND the cost for equivalent
infrastructure is lower too.)
On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich
mailto:vgarnashev...@gm
there. The performance is much
more consistent, the management UI is more intuitive, AND the cost
for equivalent infrastructure is lower too.)
On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich
mailto:vgarnashev...@gmail.com>> wrote:
Hi,
I've tri
I've done some more tests. Here they are all:
io1, 100 GB SSD, 1000 IOPS
effective_io_concurrency=0 Execution time: 40333.626 ms
effective_io_concurrency=1 Execution time: 163840.500 ms
effective_io_concurrency=2 Execution time: 162606.330 ms
effective_io_concurrency=4 Execution time: 163670.405
I did some more tests. I've made an SQL dump of the table. Then used
head/tail commands to cut the data part. Then used shuf command to
shuffle rows, and then joined the pieces back and restored the table
back into DB.
Before:
select array_agg(aid) from (select aid from pgbench_accounts order
I mean, that the issue is indeed affected by the order of rows in the
table. Random heap access patterns result in sparse bitmap heap scans,
whereas less random heap access patterns result in denser bitmap heap
scans. Dense scans have large portions of contiguous fetches, a
pattern that is quite a
I noticed that the recent round of tests being discussed never
mentioned the file system used. Was it XFS? Does changing the
agcount change the behaviour?
It was ext4.
Regards,
Vitaliy
Sorry for delay with response, I had to switch to other tasks and didn't
have time to run proper tests and write some meaningful response.
Recently, a similar issue happened with another our database, so I
decided to write an update.
Bitmap scan was preferred to index scan by the planner, bu
Hi,
I'm running the same query with "set enable_seqscan = on;" and "set
enable_seqscan = off;":
-> Nested Loop Left Join (cost=0.00..89642.86 rows=1 width=30) (actual
time=1.612..6924.232 rows=3289 loops=1)
Join Filter: (sys_user.user_id = j_6634.id)
Rows Removed by Join Filter
18 matches
Mail list logo
