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

On 08/07/2019 02:04, Rob Sargent wrote:
> 
> 
>> On Jul 7, 2019, at 6:02 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>>
>>
>>
>>> On Jul 7, 2019, at 6:01 PM, Rob Sargent <robjsargent@gmail.com <mailto:robjsargent@gmail.com>> wrote:
>>>
>>>
>>>
>>>> On Jul 7, 2019, at 5:49 PM, Tom Mercha <mercha_t@hotmail.com <mailto:mercha_t@hotmail.com>> wrote:
>>>>
>>>> On 08/07/2019 01:46, Rob Sargent wrote:
>>>>>
>>>>>
>>>>>> On Jul 7, 2019, at 5:22 PM, Tom Mercha <mercha_t@hotmail.com <mailto:mercha_t@hotmail.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
atone 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
processesfor 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