4 ms·

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

by jka 7y ago

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-statistics-io-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st... [2] - https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [3] - https://www.postgresql.org/docs/current/sql-explain.html https://www.postgresql.org/docs/current/sql-explain.html

mulander 7y ago
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.
ants_a 7y ago
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
- mulander 7y ago
  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 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)
  - jka 7y ago
    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.
holtalanm 7y ago
as others have said, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) will give you IO stats within your query at a very granular level.
marcosdumay 7y ago
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.
innocentoldguy 7y ago
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.

by jka 7y ago

mulander 7y ago

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.

ants_a 7y ago

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

mulander 7y ago
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 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)
- jka 7y ago
  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.

holtalanm 7y ago

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

marcosdumay 7y ago

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.

innocentoldguy 7y ago

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.