As a database developer, you want these sub-transactions or autonomous transactions to be processed and committed regardless of what happens to the larger surrounding transaction block.
The pg_background Postgres extension originally written by Robert Haas starts a separate worker to process a SQL statement, which is a nice way to achieve an autonomous transaction.
Partial Indexes
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');
SELECT * FROM pg_stat_activity WHERE state = 'active';
So you can identify the PID of the hanging query you want to terminate, run this:
SELECT pg_cancel_backend(PID);
CREATE TABLE books (
book_id serial NOT NULL,
data jsonb
);
INSERT INTO books VALUES (1, '{"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}');
SELECT data->'title' AS title FROM books WHERE data->'published' = 'false';
CREATE INDEX idx_published ON books ((data->'published'));
Probably the only valid reason for using
set enable_seqscan=false
is when you're writing queries and want to quickly see what the query plan would actually be were there large amounts of data in the table(s).