Add database index for authoritive_member_node field in systemmetadata table

[taojing2002]

The python submitter client for cn to use Dataone-Indexer will query distinct authoritive_member_node frequently. Now the performance is not good. We need to add an index for this field. The code will look like:

CREATE INDEX IF NOT EXISTS systemMetadata_authoritive_member_node on systemMetadata(authoritive_member_node);

[mbjones]

Sounds good @taojing2002

Can you verify that adding this specific index will speed up your query? Can you show the changes in EXPLAIN ANALYZE output with and without the index you proposed?

[taojing2002]

On cn-stage-orc-1, the query is:

EXPLAIN ANALYZE SELECT DISTINCT authoritive_member_node FROM systemmetadata;

Without the index:

QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=165965.10..165965.69 rows=59 width=21) (actual time=2069.132..2069.156 rows=95 loops=1)
   Group Key: authoritive_member_node
   Batches: 1  Memory Usage: 32kB
   ->  Seq Scan on systemmetadata  (cost=0.00..162035.88 rows=1571688 width=21) (actual time=0.016..1362.126 rows=1571688 loops=1)
 Planning Time: 0.146 ms
 JIT:
   Functions: 5
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.990 ms, Inlining 0.000 ms, Optimization 0.598 ms, Emission 8.064 ms, Total 9.652 ms
 Execution Time: 2070.360 ms
(10 rows)

With the index:

QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=165965.10..165965.69 rows=59 width=21) (actual time=1575.767..1575.790 rows=95 loops=1)
   Group Key: authoritive_member_node
   Batches: 1  Memory Usage: 32kB
   ->  Seq Scan on systemmetadata  (cost=0.00..162035.88 rows=1571688 width=21) (actual time=0.009..875.373 rows=1571688 loops=1)
 Planning Time: 0.186 ms
 JIT:
   Functions: 5
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.631 ms, Inlining 0.000 ms, Optimization 0.320 ms, Emission 6.483 ms, Total 7.433 ms
 Execution Time: 1576.514 ms
(10 rows)

The index doe not dramatically improve the query performance. Also Seq Scan still is used when the index has been generated.

[mbjones]

Did you VACUUM ANALYZE first? There's a lot of resources online explaining why DISTINCT can be slow and how to work around it.