--- title: "How Postgres chooses which indexes to use" publication: "Postgres Weekly" issue: 449 published_at: "2022-04-06" canonical_url: "https://postgresweekly.com/issues/449" --- Plus interviews with most Postgres users, and a way to use SQLite over Postgres's wire protocol. | #​449 — April 6, 2022 [Web Version](https://postgresweekly.com/issues/449) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/ovuvbxdhf0pftb24xu5l.jpg)](https://pganalyze.com/blog/how-postgres-chooses-index) [How Postgres Chooses Which Index to Use for a Query](https://pganalyze.com/blog/how-postgres-chooses-index) — Whenever a blog post is dated April 1st, I’m immediately on edge, but there’s no fooling around in Lukas’s deep yet direct look at how Postgres’s query planner gets to work. This goes right down into Postgres’s source code(!) but also covers [a practical example](https://pganalyze.com/blog/how-postgres-chooses-index#parameterized-index-scans-or-why-nested-loop-are-sometimes-a-good-join-type) of why this is useful knowledge to have in your back pocket. Lukas Fittl [Mapping Timestamps to 'Bins' with `date_bin`](https://www.cybertec-postgresql.com/en/postgresql-mapping-timestamps-date_bin/) — Somehow I missed this neat addition in Postgres 14 till now. [`date_bin`](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-BIN) is a function to place a supplied timestamp into a bin/bracket/zone defined within a set of intervals from a specified origin. It provides a finer level of control to the long standing `date_trunc`. Hans-Jürgen Schönig [![\](https://copm.s3.amazonaws.com/a32045ee.png)](https://www.polyscale.ai/platform/) [App Performance Blocked By Your Database?](https://www.polyscale.ai/platform/) — Scaling Postgres databases can mean cost and complexity. PolyScale provides a plug-and-play serverless edge cache for Postgres. Scale globally in minutes without writing code or deploying infrastructure. PolyScale.ai sponsor **In brief:** - Andreas Scherbaum has continued the fantastic [weekly Postgres user interviews](https://postgresql.life/) with recent names including [🇬🇧 Michael Christofides](https://postgresql.life/post/michael_christofides/), [🇺🇦 Taras Kloba](https://postgresql.life/post/taras_kloba/) and [🇺🇸 Claire Giordano](https://postgresql.life/post/claire_giordano/). It's a great way to learn about Postgres users all around the world. - 📅 [Postgres Silicon Valley 2022](https://postgresconf.org/conferences/SV2022) is taking place this week (in person, too) and it's one more week till [Citus Con](https://www.citusdata.com/cituscon/2022/) (which is virtual). - Bruce Momjian has [reflected on open-source licensing](https://momjian.us/main/blogs/pgblog/2022.html#April_4_2022), the 'unintended use of technology' and inability to limit use of Postgres by undesirable end users (such as the Russian war machine, in this case). [Postgres Container Apps: Easy Deployment of Apps _in_Postgres](https://blog.crunchydata.com/blog/announcing-postgres-container-apps-easy-deploy-postgres-apps) — An interesting development. _Postgres Container Apps_ provide a mechanism for spinning up a container-based app _from within Postgres_ that itself runs directly alongside your Postgres database. This is Crunchy specific but it’s interesting to think of the use cases for this. Craig Kerstiens (CrunchyData) [Identify Postgres Performance Bottlenecks with `pg_stat_statements`](https://www.timescale.com/blog/identify-postgresql-performance-bottlenecks-with-pg_stat_statements/) — `pg_stat_statements` comes up time and time again as one of the most used Postgres extensions and for good reason: it’s great for debugging performance issues. It’s now available on Timescale Cloud by default, but you can [use it on your non Timescale systems too,](https://blog.crunchydata.com/blog/tentative-smarter-query-optimization-in-postgres-starts-with-pg_stat_statements) of course. Ryan Booz (Timescale) [Quick Trick: Search in Every Field in a Table](https://www.depesz.com/2022/04/05/dba-tips-n-tricks-search-in-every-field-in-a-table/) — You want to find a value in a table and you don’t care what column it’s in! What to do? You could select a row as a single value and cast it to a string.. Hubert depesz Lubaczewski [Keep Up with the Latest in Startups, Tech, & Programming in Just 5 Min](https://tldr.tech/signup?utm_source=pgw0406) TLDR Newsletter sponsor [Tidying Up with `VACUUM`](https://www.enterprisedb.com/blog/pg-phriday-tidying-vacuum) — Look, we can’t go a month without covering vacuuming in some way or another. It’s not as fun as [extreme ironing](https://en.wikipedia.org/wiki/Extreme_ironing) but this walk through ensuring the vacuuming process is properly configured for your database’s requirements is a lot more useful.. and Shaun covers it here. Shaun Thomas (EDB) [How to Enable SSL for Postgres Connections](https://dev.to/yugabyte/how-to-enable-ssl-for-postgres-connections-5321) Frits Hoogland (Yugabyte) **🔧 Code and Tools:** [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/pyeyimxwiwphqomv5qdj.jpg)](https://eggerapps.at/postico/) [Postico: A Native Postgres Client for macOS](https://eggerapps.at/postico/) — It’s a commercial product (with a trial) but has an elegant native macOS aesthetic. We first linked to it seven years ago, but love checking in to see projects still doing well. Egger Apps [A Postgres Wire-Compatible SQLite Proxy](https://github.com/benbjohnson/postlite) — What if you could use Postgres client tools.. but to work with SQLite databases on remote servers? That’s the idea put forward by this interesting experiment. Ben Johnson **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)