Forcing a sequential scan over the index scan

All we need is an easy explanation of the problem, so here it is.

I have this query

select
    s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from
    part,supplier,partsupp,nation,region
where
    p_partkey=ps_partkey
and
    s_suppkey=ps_suppkey
and
    s_nationkey=n_nationkey
and
    n_regionkey=r_regionkey
and
    p_size=15
and
    p_type like '%BRASS'
and
    r_name='EUROPE'
and
    ps_supplycost=1.0
ORDER by
    s_acctbal desc ,n_name,s_name,p_partkey;

and it uses p_partkey, s_suppkey, n_nationkey and r_regionkey as primary keys so the query plan is

[
  {
    "Plan": {
      "Node Type": "Sort",
      "Parallel Aware": false,
      "Async Capable": false,
      "Actual Rows": 0,
      "Actual Loops": 1,
      "Sort Key": [
        "supplier.s_acctbal DESC",
        "nation.n_name",
        "supplier.s_name",
        "part.p_partkey"
      ],
      "Sort Method": "quicksort",
      "Sort Space Used": 25,
      "Sort Space Type": "Memory",
      "Plans": [
        {
          "Node Type": "Nested Loop",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Join Type": "Inner",
          "Actual Rows": 0,
          "Actual Loops": 1,
          "Inner Unique": true,
          "Join Filter": "(nation.n_regionkey = region.r_regionkey)",
          "Rows Removed by Join Filter": 0,
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Join Type": "Inner",
              "Actual Rows": 0,
              "Actual Loops": 1,
              "Inner Unique": true,
              "Plans": [
                {
                  "Node Type": "Nested Loop",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Join Type": "Inner",
                  "Actual Rows": 0,
                  "Actual Loops": 1,
                  "Inner Unique": true,
                  "Plans": [
                    {
                      "Node Type": "Gather",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Async Capable": false,
                      "Actual Rows": 0,
                      "Actual Loops": 1,
                      "Workers Planned": 2,
                      "Workers Launched": 2,
                      "Single Copy": false,
                      "Plans": [
                        {
                          "Node Type": "Nested Loop",
                          "Parent Relationship": "Outer",
                          "Parallel Aware": false,
                          "Async Capable": false,
                          "Join Type": "Inner",
                          "Actual Rows": 0,
                          "Actual Loops": 3,
                          "Inner Unique": true,
                          "Workers": [],
                          "Plans": [
                            {
                              "Node Type": "Seq Scan",
                              "Parent Relationship": "Outer",
                              "Parallel Aware": true,
                              "Async Capable": false,
                              "Relation Name": "partsupp",
                              "Alias": "partsupp",
                              "Actual Rows": 30,
                              "Actual Loops": 3,
                              "Filter": "(ps_supplycost = 1.0)",
                              "Rows Removed by Filter": 2666636,
                              "Workers": []
                            },
                            {
                              "Node Type": "Memoize",
                              "Parent Relationship": "Inner",
                              "Parallel Aware": false,
                              "Async Capable": false,
                              "Actual Rows": 0,
                              "Actual Loops": 91,
                              "Cache Key": "partsupp.ps_partkey",
                              "Cache Mode": "logical",
                              "Cache Hits": 0,
                              "Cache Misses": 29,
                              "Cache Evictions": 0,
                              "Cache Overflows": 0,
                              "Peak Memory Usage": 2,
                              "Workers": [
                                {
                                  "Worker Number": 0,
                                  "Cache Hits": 0,
                                  "Cache Misses": 29,
                                  "Cache Evictions": 0,
                                  "Cache Overflows": 0,
                                  "Peak Memory Usage": 2
                                },
                                {
                                  "Worker Number": 1,
                                  "Cache Hits": 0,
                                  "Cache Misses": 33,
                                  "Cache Evictions": 0,
                                  "Cache Overflows": 0,
                                  "Peak Memory Usage": 3
                                }
                              ],
                              "Plans": [
                                {
                                  "Node Type": "Index Scan",
                                  "Parent Relationship": "Outer",
                                  "Parallel Aware": false,
                                  "Async Capable": false,
                                  "Scan Direction": "Forward",
                                  "Index Name": "part_pkey",
                                  "Relation Name": "part",
                                  "Alias": "part",
                                  "Actual Rows": 0,
                                  "Actual Loops": 91,
                                  "Index Cond": "(p_partkey = partsupp.ps_partkey)",
                                  "Rows Removed by Index Recheck": 0,
                                  "Filter": "((p_type ~~ '%BRASS'::text) AND (p_size = 15))",
                                  "Rows Removed by Filter": 1,
                                  "Workers": []
                                }
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "Node Type": "Index Scan",
                      "Parent Relationship": "Inner",
                      "Parallel Aware": false,
                      "Async Capable": false,
                      "Scan Direction": "Forward",
                      "Index Name": "supplier_pkey",
                      "Relation Name": "supplier",
                      "Alias": "supplier",
                      "Actual Rows": 0,
                      "Actual Loops": 0,
                      "Index Cond": "(s_suppkey = partsupp.ps_suppkey)",
                      "Rows Removed by Index Recheck": 0
                    }
                  ]
                },
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Scan Direction": "Forward",
                  "Index Name": "nation_pkey",
                  "Relation Name": "nation",
                  "Alias": "nation",
                  "Actual Rows": 0,
                  "Actual Loops": 0,
                  "Index Cond": "(n_nationkey = supplier.s_nationkey)",
                  "Rows Removed by Index Recheck": 0
                }
              ]
            },
            {
              "Node Type": "Seq Scan",
              "Parent Relationship": "Inner",
              "Parallel Aware": false,
              "Async Capable": false,
              "Relation Name": "region",
              "Alias": "region",
              "Actual Rows": 0,
              "Actual Loops": 0,
              "Filter": "(r_name = 'EUROPE'::bpchar)",
              "Rows Removed by Filter": 0
            }
          ]
        }
      ]
    },
    "Triggers": []
  }
]

as you can see it requires around 13 seconds to perform my query. I would like to see how many seconds would that query require if those primary keys were not be created. That is I would like to force my optimizer to choose the sequential scan over the index scan.

  • How can I do?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

set enable_indexscan TO off; set enable_bitmapscan TO off;

solves

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply