Re: Measuring the Query Optimizer Effect: Turning off the QO? - Mailing list pgsql-general

On Jul 7, 2019, at 6:29 PM, Rob Sargent <robjsargent@gmail.com> wrote:

O

Sorry, maybe my question wasn't clear enough.

A query can be rewritten in various ways by applying rules and costs of
relational algebra operators, as well as their parallelisation. I am
talking about turning off this query optimization, so I am already
assuming that indexes aren't present.

Have you played with any of these settings?

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# select name, setting, unit,short_desc from pg_settings where name ~ 'para';
              name               | setting | unit |                                             short_desc
---------------------------------+---------+------+----------------------------------------------------------------------------------------------------
 force_parallel_mode             | off     |      | Forces use of parallel query facilities.
 max_parallel_workers            | 16      |      | Sets the maximum number of parallel workers that can be active at one time.
 max_parallel_workers_per_gather | 8       |      | Sets the maximum number of parallel processes per executor node.
 min_parallel_index_scan_size    | 64      | 8kB  | Sets the minimum amount of index data for a parallel scan.
 min_parallel_table_scan_size    | 1024    | 8kB  | Sets the minimum amount of table data for a parallel scan.
 parallel_setup_cost             | 1000    |      | Sets the planner's estimate of the cost of starting up worker processes for parallel query.
 parallel_tuple_cost             | 0.1     |      | Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend.
 ssl_dh_params_file              |         |      | Location of the SSL DH parameters file.
(8 rows)

Well not the last one of course.

Better yet, “where category ~* ‘planner’"

Hi

Yes, I've taken a look. I'm just interested in turning off the whole 
module and there is no parameter to do just that - an on and off switch 
- from what I can understand. What I can do instead is to go over the 
entire list of parameters and configure them each to generate a bad 
plan, but I'm not sure in general how to make a configuration which 
gives me the worst plan possible under all situations.

I was hoping that someone has the on/off switch I'm looking for or a 
parameter template which has been used for the same purpose or something 
along these lines...

This document describes the module I'm talking about: 
https://www.postgresql.org/docs/current/planner-optimizer.html

Yeah, I guess I read that to say postgres will optimize generically (after geqo_threshold). Now maybe that’s the worst plan? Per force a lot of seq-scans in the absence of any indices.  And if it isn’t the worst possible plan, so what, you’re using postgres, you won’t get a worse plan without lying to the optimizer.

But I’m clearly in over my head.  The pros will be along shortly, I’m sure.