I have installed a b-tree index on o_orderdate attribute.

I have this query:

explain select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate>='01/07/1993'
and
    o_orderdate<'01/10/1993'
and
    exists (select * from commitdate_before_receiptdate_view) -- mat view
GROUP by
    o_orderpriority
ORDER by
    o_orderpriority

this is giving me:

"Finalize GroupAggregate  (cost=734229.48..734230.75 rows=5 width=24)"
"  Group Key: orders.o_orderpriority"
"  InitPlan 1 (returns $0)"
"    ->  Seq Scan on commitdate_before_receiptdate_view  (cost=0.00..1714549.24 rows=33184824 width=0)"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24)"
"        Workers Planned: 2"
"        Params Evaluated: $0"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24)"
"              Sort Key: orders.o_orderpriority"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24)"
"                    Group Key: orders.o_orderpriority"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16)"
"                          One-Time Filter: $0"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"

How can I check whether my query is using the index I installed on o_orderdate attribute?

According to this article, the optimizer has determined that parallel query is the fastest solution, creating a Gather node. So it isn't using the B index on o_orderdate?

CodePudding user response：

It is not using the index, but 'parallel' does not show us that, because index scans can also be done in parallel. It is the presence of 'seq scan' and absence of '[parallel] [bitmap] index [only] scan' which tells us that. A plain EXPLAIN is enough to tell us that it doesn't use the index, but can't tell us much about why it chose not to, which I assume is what you are trying to get at.