Search Results

Quote: I can't do an explain analyze on temporary tables to detect expensive operations. It's not common to execute EXPLAIN ANALYZE inside a function but it's doable in plpgsql, with either: explai …

with the count(), I want to get the count of all the rows present irrespective of the where clause. This can be done with an independent subquery in the select-list, if it returns a single value, as …

If you have no plan to use these files for recovery, you can temporarily set archive_command to a command that succeeds but does nothing. The server will call it on each WAL file to archive and then d …

I would expect that after shutting down all other sessions and running a VACUUM ANALYSE, the way the tables are used wouldn't make any difference. It's the VACUUM ANALYZE that does not make a …

Assuming all other columns could be stored in the non-toasted table, would an update to column b require writing column a again? No. According to the documentation (in Out-of-Line, On-Disk TOAST Stor …

I'm not sure, if PostgreSQL each time of this update will overwrite full file or only one bit in the hard disk? PostgreSQL will definitely rewrite the whole column on any change, as it cannot d …

The execution plan shown does not seem to match the big SELECT DISTINCT query because the Sort and Unique steps are missing. Anyway you are correct than when retrieving ~50% of a table, index don't he …

Your mistake is to always take the first row of the sample. Take a random row instead: SELECT * FROM (SELECT column FROM table TABLESAMPLE BERNOULLI(1)) AS s ORDER BY RANDOM() LIMIT 1; The cont …

Postgres compresses automatically large objects chunks, as well as bytea through the TOAST mechanism. But most PDFs are already compressed. As an example I have 1000 dedup-ed pdfs in my mailbox store …

First you may try two things: Do not use WITH because it's an optimization fence. As of PostgreSQL 10, the result of the CTE query will be materialized even if it would be better not to. Use NOT EXI …

The formula that probably explains your case is (as documented in The Autovacuum Daemon): ...if the number of tuples obsoleted since the last VACUUM exceeds the "vacuum threshold", the table is …

This looks like a fairly generic full-text search problem in a relational database. Your prediction that updates in actors or objects would be troublesome in a denormalized structure looks spot-on. B …

Q: what is the key for plan cache in Postgres? It's the prepared statement associated to the query if there is one, and it does not happen automatically. Outside of prepared statements, there is no …

The extract from pgadmin.log shows that for each output column of the result, pgadmin executes two queries against the catalog, one to obtain the formatted name of the type from its oid, another to ob …

Q: SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%') which gives what I want, but it always does a seq scan of the table, even though that I have an index on the do …