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

> On Jul 7, 2019, at 6:02 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>
>
>> On Jul 7, 2019, at 6:01 PM, Rob Sargent <robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>> wrote:
>>
>>
>>
>>> On Jul 7, 2019, at 5:49 PM, Tom Mercha <mercha_t(at)hotmail(dot)com <mailto:mercha_t(at)hotmail(dot)com>> wrote:
>>>
>>> On 08/07/2019 01:46, Rob Sargent wrote:
>>>>
>>>>
>>>>> On Jul 7, 2019, at 5:22 PM, Tom Mercha <mercha_t(at)hotmail(dot)com <mailto:mercha_t(at)hotmail(dot)com>> 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 <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’"