0

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

1 Answer 1

1
set enable_indexscan TO off; set enable_bitmapscan TO off;

solves

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.