Hacker News Reader

Comment by jka | Hacker News Reader

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance.

An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results.

PostgreSQL has good query plan output via the EXPLAIN[2] statement - but I haven't (yet?) seen PostgreSQL produce per-query IO statistics.

[1] - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st...

[2] - https://docs.microsoft.com/en-us/sql/relational-databases/pe...

[3] - https://www.postgresql.org/docs/current/sql-explain.html

View on HN

Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold:

    shared_preload_libraries = 'auto_explain'
    auto_explain.log_min_duration = '5s'
    auto_explain.log_analyze = true
    auto_explain.log_buffers = true
    auto_explain.log_timing = false

You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper.

Table F.21. pg_stat_statements Columns

https://www.postgresql.org/docs/11/pgstatstatements.html

blk_read_time

double precision

Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time

double precision

Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

Thanks for this - 'pg_stat_statements' definitely looks like a similar feature, in particular because it reflects the count of disk-related operations (not just I/O time).

It took some brief configuration but I've been able to try it out locally and will refer to it when doing PostgreSQL query performance tuning in future.

PostgreSQL has track_io_timing and passing buffers will then include IO timing.

\set track_io_timing=on;

explain (analyze, verbose, buffers) your-query;

Additionally, it will also include things like times for triggers executed from the query.

In the end of the day, the PostgreSQL optimizer is just great, and will probably beat anything in a well balanced comparison.

But MSSQL also has a fairy good optimizer (from the ones on the GP relation, those are the only two good options) so you can set things to get better than an unset Postgres. It is also possible (but unlikely) that you get a problem where it fits better.

About Oracle, it is pretty great on doing `select stuff from table join table using (column)`. (I dunno what it does, but it does spend a lot of memory doing it.) So if you only does that, it's the best option. But if you do anything else, that anything else will completely dominate the execution time.

For me, MySQL’s quirkiness and lesser adherence to the SQL standard kind of neuter any performance niftiness it might have. I can see using MySQL for something like logging but for production data, I prefer PostgreSQL. It’s much more predictable.

as others have said, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) will give you IO stats within your query at a very granular level.

Reply on news.ycombinator.com