Re: Optimising a two column OR check
On Sat, 12 Oct 2019 at 17:16, Andrew Gierth wrote: > > "Ivan" == Ivan Voras writes: > > Ivan> SELECT user1_id,user2_id FROM friend WHERE user1_id=42 OR > user2_id=42; > > To get friends of user 42: > > SELECT user1_id FROM friend WHERE user2_id=42 > UNION ALL > SELECT user2_id FROM friend WHERE user1_id=42; > > assuming you create the (user2_id,user1_id) index, this should get you > an Append of two index-only scans. We can use UNION ALL here rather than > UNION because the table constraints ensure there are no duplicates. > Thanks! That's a more elegant solution for my query than what I had in mind!
pg_stat_bgwriter
Dear I would like to share with you to see what you think about the statistics of pg_stat_bgwriter postgres = # select * from pg_stat_bgwriter; checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_ backend_fsync | buffers_alloc | stats_reset --- + - + --- + -- + --- - + --- + -- + - + - -- + --- + --- 338 | 6 | 247061792 | 89418 | 2939561 | 19872289 | 54876 | 6015787 | 0 | 710682240 | 2019-10-06 19: 25: 30.688186-03 (1 row) postgres = # show bgwriter_delay; bgwriter_delay 200ms (1 row) postgres = # show bgwriter_lru_maxpages; bgwriter_lru_maxpages --- 100 (1 row) postgres = # show bgwriter_lru_multiplier; bgwriter_lru_multiplier - 2 (1 row) Do you think it should increase bgwriter_lru_maxpages due to the value of maxwritten_clean? Do you think it should increase bgwriter_lru_maxpages, bgwriter_lru_multiplier, and decrease bgwriter_delay due to the value of buffers_backend compared to buffers_alloc? Do you think a modification is necessary? What values would you recommend? thank you -- Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
