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?
Best Answer
solves