Forcing a sequential scan over the index scan



  • 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?


  • set enable_indexscan TO off; set enable_bitmapscan TO off;
    

    solves




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2