Case study: optimization of weirdly picked bad plan

Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and...

Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3. Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂

On 1st of November 2024, Michael Paquier committed patch: Add SQL function array_reverse() This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle(). The implementation structure is inspired by array_shuffle(), with a subroutine...

PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it. What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some … Continue reading "New...

Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do? Let's see if I can shed some light on it… For starters: SQL/JSON is … Continue reading "SQL/JSON is here! (kinda “Waiting for Pg 17”)"