Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent <robjsargent@xxxxxxxxx>]

On Jul 7, 2019, at 6:02 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:

On Jul 7, 2019, at 6:01 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:

On Jul 7, 2019, at 5:49 PM, Tom Mercha <mercha_t@xxxxxxxxxxx> wrote:

On 08/07/2019 01:46, Rob Sargent wrote:

On Jul 7, 2019, at 5:22 PM, Tom Mercha <mercha_t@xxxxxxxxxxx> wrote:

Hi All

As we know, a query goes through number of stages before it is executed.
One of these stages is query optimization (QO).

There are various parameters to try and influence optimizer decisions
and costs. But I wanted to measure the effect of such a stage by turning
it off completely and I can't find such a parameter which explicitly
does that. Then I could execute a query to get the effect of "QO active
and "QO inactive" and compare.

Obviously, I know well what the results would generally look like but I
am just interested in measuring the differences for various types of
queries. I am also aware that this is a simple comparison - there are
https://gitlab.com/camplab/jpsgcs interesting comparisons to perform with QO tweaks, but right now I
am interested in something basic.

So how would one shut down QO? Or at least, obtaining the guarantee of
generating the worst plan possible, ideally without touching many
parameters?

Best,
Tom

Drop all indices?

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’"