Postgres Pro
Downloads
Home   >   mailing lists

RE: Weird seqscan node plan - Mailing list pgsql-general

From Igor Neyman
Subject RE: Weird seqscan node plan
Date
Msg-id BN6PR1701MB1890CCEA39CD56F1103FD772DA450@BN6PR1701MB1890.namprd17.prod.outlook.com
Whole thread Raw
In response to Weird seqscan node plan  (Игорь Выскорко <vyskorko.igor@yandex.ru>)
List pgsql-general
Tree view

From: Игорь Выскорко [mailto:vyskorko.igor@yandex.ru]
Sent: Tuesday, November 26, 2019 4:13 AM
To: Andrei Zhidenkov <andrei.zhidenkov@n26.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Weird seqscan node plan

 

26.11.2019, 16:02, "Andrei Zhidenkov" <andrei.zhidenkov@n26.com>:

How many tables do you have in your query? If too many, in your case “Genetic Query Optiomiation” might be used (https://www.postgresql.org/docs/10/geqo-pg-intro.html).

 

On 26. Nov 2019, at 03:19, Игорь Выскорко <vyskorko.igor@yandex.ru> wrote:

 

Hi all!
I'm confused by planner choice: seqscan but not index scan when index scan cost is much cheaper.
1st plan: https://explain.depesz.com/s/Cti#l8
2nd plan (with "set enable_seqscan = off"): https://explain.depesz.com/s/qn0I#l8

Look at 8th row: this nested loop decided to join the second node by using seqscan (1st plan) when index is available (2nd plan). Index scan is much cheaper (0.430 over 257.760).

What am I missing?

And thanks for any reply!
 

 

Hm... about 12 tables.
I tried to disable geqo (set geqo = off;)  plan didn't change. But thanks for your try )

Version I'm using (if matter):
select version();

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

 

Try increasing the following parameters to 14 (or even 16, if you are not sure about number of tables involved):

 

geqo_threshold = 14

from_collapse_limit = 14

join_collapse_limit = 14

 

“about 12” is too close to default limit, 12.

 

Regards,

Igor Neyman

 

pgsql-general by date:

Previous
From: stan
Date:
Subject: pgmodeler an server V12
Next
From: Joe Conway
Date:
Subject: Re: I think that my data is saved correctly, but when printing again,other data appears