Re: Is EXISTS the most efficient approach for PostgreSql to check for existence of nodes in a tree?

Trying to reply to Rob:

Apologies if this does not end up in the thread (gmail is just driving me mad, I can't seem to receive messages, so I've subscribed again)

For some reason Limit 1 cause my query to go on for minutes without a response, which was not the case.

The following query takes about 10.6 seconds, with a table that has about 20 million rows. First, the indices of the table with create statement:

CREATE TABLE public.path_value (
  val_string TEXT,
  feature_mapping_id INTEGER NOT NULL,
  parent_feature_mapping_id INTEGER,
  feature_name TEXT,
  rm_type_name TEXT,
  path INTEGER NOT NULL,
  payload_id INTEGER NOT NULL,
  id INTEGER NOT NULL,
  ehr_id INTEGER,
  CONSTRAINT path_value_pkey PRIMARY KEY(id)
) WITHOUT OIDS;

CREATE INDEX path_value_idx ON public.path_value
  USING btree (rm_type_name COLLATE pg_catalog."default", feature_name COLLATE pg_catalog."default");

CREATE INDEX path_value_idx1 ON public.path_value
  USING btree (feature_mapping_id, parent_feature_mapping_id);

CREATE INDEX path_value_idx2 ON public.path_value
  USING btree (payload_id);


Now results of EXPLAIN ANALYZE:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=543422.34..571013.45 rows=5 width=4) (actual time=5854.267..10684.798 rows=82003 loops=1)"
"  Join Filter: (root.feature_mapping_id = anodeid.parent_feature_mapping_id)"
"  ->  Merge Join  (cost=543422.34..543426.29 rows=12 width=24) (actual time=5854.182..5953.531 rows=82003 loops=1)"
"        Merge Cond: ((root.feature_mapping_id = node1.parent_feature_mapping_id) AND (root.payload_id = node2.payload_id))"
"        ->  Sort  (cost=1150.65..1151.35 rows=283 width=12) (actual time=359.940..380.707 rows=82003 loops=1)"
"              Sort Key: root.feature_mapping_id, root.payload_id"
"              Sort Method: external merge  Disk: 1768kB"
"              ->  Index Scan using path_value_idx on path_value root  (cost=0.00..1139.12 rows=283 width=12) (actual time=0.073..261.240 rows=82003 loops=1)"
"                    Index Cond: ((rm_type_name = 'COMPOSITION'::text) AND (feature_name = 'composition'::text))"
"        ->  Sort  (cost=542271.69..542272.31 rows=247 width=12) (actual time=5494.236..5516.788 rows=82003 loops=1)"
"              Sort Key: node1.parent_feature_mapping_id, node2.payload_id"
"              Sort Method: external sort  Disk: 2088kB"
"              ->  HashAggregate  (cost=542259.40..542261.87 rows=247 width=12) (actual time=5377.761..5394.453 rows=82003 loops=1)"
"                    ->  Hash Semi Join  (cost=506297.37..542253.38 rows=1205 width=12) (actual time=2788.758..5325.838 rows=164004 loops=1)"
"                          Hash Cond: ((node1.payload_id = node2.payload_id) AND (node1.feature_mapping_id = node2.parent_feature_mapping_id))"
"                          ->  Bitmap Heap Scan on path_value node1  (cost=77.61..9421.62 rows=2464 width=12) (actual time=76.628..2430.473 rows=246005 loops=1)"
"                                Recheck Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))"
"                                ->  Bitmap Index Scan on path_value_idx  (cost=0.00..77.00 rows=2464 width=0) (actual time=75.640..75.640 rows=246005 loops=1)"
"                                      Index Cond: ((rm_type_name = 'CONTENTITEM'::text) AND (feature_name = 'content'::text))"
"                          ->  Hash  (cost=498674.38..498674.38 rows=399092 width=8) (actual time=2711.653..2711.653 rows=410015 loops=1)"
"                                Buckets: 4096  Batches: 16  Memory Usage: 1013kB"
"                                ->  Bitmap Heap Scan on path_value node2  (cost=10345.32..498674.38 rows=399092 width=8) (actual time=71.718..2607.467 rows=410015 loops=1)"
"                                      Recheck Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)"
"                                      ->  Bitmap Index Scan on path_value_idx  (cost=0.00..10245.55 rows=399092 width=0) (actual time=69.634..69.634 rows=410015 loops=1)"
"                                            Index Cond: (rm_type_name = 'ITEMSTRUCTURE'::text)"
"  ->  Index Scan using path_value_idx2 on path_value anodeid  (cost=0.00..2298.91 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=82003)"
"        Index Cond: (payload_id = node2.payload_id)"
"        Filter: ((feature_name = 'archetypeNodeId'::text) AND (val_string = 'openEHR-EHR-COMPOSITION.discharge.v1'::text))"
"Total runtime: 10692.277 ms"

and finaly, the query that gave this result:

EXPLAIN ANALYZE SELECT root.id from path_value as root
    WHERE
        root.rm_type_name = 'COMPOSITION'
    AND
        root.feature_name = 'composition'
    AND
    EXISTS (SELECT anodeid.id from path_value as anodeid
            WHERE
                    anodeId.parent_feature_mapping_id = root.feature_mapping_id
            AND       
                    anodeId.payload_id = root.payload_id
            AND
                    anodeId.feature_name = 'archetypeNodeId'
            AND
                    anodeId.val_string = 'openEHR-EHR-COMPOSITION.discharge.v1'           
            )
           
    AND
    EXISTS (SELECT 1 from path_value as node1
             WHERE
                node1.payload_id = root.payload_id             
            AND
                node1.parent_feature_mapping_id = root.feature_mapping_id
            AND
                node1.feature_name = 'content'
            AND
                node1.rm_type_name = 'CONTENTITEM'
            AND
            EXISTS (SELECT 1 from path_value as node2
                    WHERE
                    node2.payload_id = node1.payload_id
                    AND
                    node2.parent_feature_mapping_id = node1.feature_mapping_id
                    AND
                    node2.rm_type_name = 'ITEMSTRUCTURE'
                    )
            )

Is there a glaring error in my approach? Should I be better off with another SQL query, or Ltree/XPATH queries?

Best regards
Seref