As of now, explain.depesz.com will provide hints for seq scans with multiple equality conditions joined with AND.
select * from table where proj_type = 'GR14' AND week = 2000 AND pop_flag = '1'
You can see it in action in this plan.
Hope you'll find it useful.
Someone asked recently on Slack, whether one should build tsvector data in a field, and index it, or make index on expression.
We talked about it for a while, and I thought I'll reformat my thoughts into blogpost to avoid retyping it next time.
Continue reading How to index data with tsearch?
Following is text provided by organizers. On my end I can say that I'll be there, and I'm on “Talk selection team" 🙂
The first ever Polish PostgreSQL conference is approaching faster than we think.
On April 22, 2022, we invite you to spend the whole day gaining top technical knowledge and broadening your horizons. PGDayPL 2022 is a great gaining opportunity to talk to the amazing speakers and spend time in the accompaniment of what we are most passionate about.
The conference is held at the POLIN Conference Center in Warsaw's Muranów.
What can you expect?
- fantastic lectures where you will gain technical knowledge – in both Polish and English!
- panels led by people with a passion for PostgreSQL and IT
- gifts for all conference attendees and additional bonuses for our speakers
- delicious catering for guests
- integration meeting after a successful conference
Learn more: https://pgday.pl/.
I just pushed change to explain.depesz.com that allows for processing and displaying hints for plans.
For example, take a look at this plan, and check if you'll notice subtle “HINTS" tab.
In there you will see example hints – one about sort and memory, and the other about missing index.
It is not much, but it's a step in (hopefully) right direction, when the explain tool will also provide, automatically, some ideas on what to do to make the thing faster.
Hope you'll find it useful.
Lately there have been couple of discussions on IRC, Slack, and Reddit that showed that people assume that by using int4/integer they use 4 bytes less than they would in case of int8/bigint. This is not really the case. Let me explain why.
Continue reading How much disk space you can save by using INT4/INT instead of INT8/BIGINT?
On 3rd of February 2022, Robert Haas committed patch:
Allow archiving via loadable modules.
Running a shell command for each file to be archived has a lot of
overhead and may not offer as much error checking as you want, or the
exact semantics that you want. So, offer the option to call a loadable
module for each file to be archived, rather than running a shell command.
Also, add a 'basic_archive' contrib module as an example implementation
that archives to a local directory.
Nathan Bossart, with a little bit of kibitzing by me.
Continue reading Waiting for PostgreSQL 15 – Allow archiving via loadable modules.
On 3rd of February 2022, Peter Eisentraut committed patch:
Add UNIQUE null treatment option
The SQL standard has been ambiguous about whether null values in
unique constraints should be considered equal or not. Different
implementations have different behaviors. In the SQL:202x draft, this
has been formalized by making this implementation-defined and adding
an option on unique constraint definitions UNIQUE [ NULLS [NOT]
DISTINCT ] to choose a behavior explicitly.
This patch adds this option to PostgreSQL. The default behavior
remains UNIQUE NULLS DISTINCT. Making this happen in the btree code
is pretty easy; most of the patch is just to carry the flag around to
all the places that need it.
The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.
I named all the internal flags, catalog columns, etc. in the negative
("nulls not distinct") so that the default PostgreSQL behavior is the
default if the flag is false.
Reviewed-by: Maxim Orlov <email@example.com>
Reviewed-by: Pavel Borisov <firstname.lastname@example.org>
Continue reading Waiting for PostgreSQL 15 – Add UNIQUE null treatment option
On 28th of January 2022, Peter Eisentraut committed patch:
Add HEADER support to COPY text format
The COPY CSV format supports the HEADER option to output a header
line. This patch adds the same option to the default text format. On
input, the HEADER option causes the first line to be skipped, same as
Author: Rémi Lapeyre <email@example.com>
Continue reading Waiting for PostgreSQL 15 – Add HEADER support to COPY text format
On 17th of January 2022, Michael Paquier committed patch:
"jsonlog" is a new value that can be added to log_destination to provide
logs in the JSON format, with its output written to a file, making it
the third type of destination of this kind, after "stderr" and
"csvlog". The format is convenient to feed logs to other applications.
There is also a plugin external to core that provided this feature using
the hook in elog.c, but this had to overwrite the output of "stderr" to
work, so being able to do both at the same time was not possible. The
files generated by this log format are suffixed with ".json", and use
the same rotation policies as the other two formats depending on the
This takes advantage of the refactoring work done previously in ac7c807,
bed6ed3, 8b76f89 and 2d77d83 for the backend parts, and 72b76f7 for the
TAP tests, making the addition of any new file-based format rather
The documentation is updated to list all the keys and the values that
can exist in this new format. pg_current_logfile() also required a
refresh for the new option.
Author: Sehrope Sarkuni, Michael Paquier
Reviewed-by: Nathan Bossart, Justin Pryzby
Continue reading Waiting for PostgreSQL 15 – Introduce log_destination=jsonlog
Back in 2018 I wrote why-upgrade.depesz.com – aggregator of changelogs between various versions of Pg.
Want to know what you will get when upgrading from 12.1 to 12.9? Here you go. Longer time changes? Like, from 9.5.20 to 14.1? I've got your back.
It even has a way to list every change that relates to anything related to indexes.
Today I updated the code, again to show which GUCs (configuration parameters) have changed between given versions. For example, getting diff from 13.5 to 14.1 shows you that:
- two parameters were removed, and are no longer there
- there are 17 new parameters
- three parameters had their default values changed
And each GUC that is listed is (well, is supposed to but in some cases it can't) linked to relevant part of docs that describe what it is.
To make it work I compiled every version of Pg, since 7.2 (there have been 410 of them!), and extracted list of config params, and their default values.
Then, I fetched docs for all major versions of Pg, and extracted list of documentation fragments that relate to each config parameter.
This will require more work on each subsequent release, but I think I can manage it.
Any way – hope you'll find it helpful.