On 08/07/2019 02:04, Rob Sargent wrote: > > >> 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 <mailto:robjsargent@xxxxxxxxx>> wrote: >>> >>> >>> >>>> On Jul 7, 2019, at 5:49 PM, Tom Mercha <mercha_t@xxxxxxxxxxx <mailto: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 <mailto: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 <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’" > 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
