Re: Bad query plan when you add many OR conditions
Marco Colli schrieb am 10.01.2020 um 02:11: > I have a query on a large table that is very fast (0s): > https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt > > Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 > OR tag5... > > However if you increase the number of OR at some point PostgreSQL makes the > bad decision to change its query plan! And the new plan makes the query > terribly slow: > https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt > > Instead of this (which is fast): > Bitmap Index Scan on index_subscriptions_on_project_id_and_tags > It starts using this (which is slow): > Parallel Index Scan using index_subscriptions_on_project_id_and_created_at > The choice seems quite stupid since it doesn't have the tags on the new > index... and indeed the query takes about 1 minute instead of a few > milliseconds. Here's a list of the available indexes: > https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt > > How can I encourage PostgreSQL to use the Bitmap Index Scan even when there > are many OR conditions? I have tried with VACUUM ANALYZE subscriptions but it > doesn't help. > > Note: the query is generated dynamically by customers of a SaaS, so I don't > have full control on it Can you replace the many ORs with a single "overlaps" comparison? This (tags @> ARRAY['crt:2018_04']::varchar[]) OR (tags @> ARRAY['crt:2018_05']::varchar[]) OR (tags @> ARRAY['crt:2018_06']::varchar[]) is equivalent to tags && array['crt:2018_04','crt:2018_05','crt:2018_06', ...] The && operator can make use of a GIN index so maybe that uses the index_subscriptions_on_project_id_and_tags regardless of the number of elements.
shared buffers and startup process
Hi, I have a master slave setup with streaming replication. I have a lots of wal files that were moved from master but not applied yet , a big delay in the replica. The replica is not working on hotstandby mode , no conflicts to delay wals apply on it. I would like to know if increasing the amount of shared-buffers could help the startup process applying the wals. I would like to know if in the process of reading the wals and applying them, blocks that should be written will be brought to shared buffer or not?? If yes, having a bigger shared buffer will keep as much as possible the amount of pages there and increase the startup process's speed avoiding pages's replacement and going to the OS cache and maybe to the disk . Does it make sense? Thanks in advanced. Regards, Joao
Re: shared buffers and startup process
On Tue, Jan 14, 2020 at 04:29:51PM +0100, Joao Junior wrote: > I would like to know if increasing the amount of shared-buffers could help > the startup process applying the wals. I would like to know if in > the process of reading the wals and applying them, blocks that should be > written will be brought to shared buffer or not?? Please feel free to look at XLogReadBufferForRedo() in xlogutils.c and check what the routine does, and when/where it gets called. The code is well-documented, so you will find your answer easily. > If yes, having a bigger > shared buffer will keep as much as possible the amount of pages there and > increase the startup process's speed avoiding pages's replacement and going > to the OS cache and maybe to the disk . > Does it make sense? It does. Even if relying on the OS cache would be enough in most cases, it is good to keep a certain of pages hot enough, and you need to be careful with not setting shared_buffers too high either. -- Michael signature.asc Description: PGP signature
