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

On Thu, May 17, 2012 at 8:40 PM, Seref Arikan <serefarikan@gmail.com> wrote:
> Is there a glaring error in my approach? Should I be better off with another
> SQL query, or Ltree/XPATH queries?

For the particular query you posted, I would suggest the following indexes:

(rm_type_name, payload_id, parent_feature_mapping_id)
And maybe:
(rm_type_name, feature_name, payload_id, parent_feature_mapping_id)

But overall, storing a hierarchical XML structure as rows in a table
might not be the best approach. If performance is problematic, you
might consider storing whole XML documents -- or fragments -- in an
xml field and create expression indexes for the queries that you need,
possibly with GIN/GiST.

Now I haven't needed to do this myself, so what follows is just me
trying out stuff to give you some ideas and certainly not "best
practice" -- there are lots of different indexing strategies and
different ways to do this.

For example:

CREATE TABLE foo (doc_id serial primary key, doc xml not null);
CREATE INDEX foo_doc_id_exists_root_element_test ON foo (doc_id) WHERE
xpath_exists('/root/element[text()="test"]', doc);
CREATE INDEX foo_root_element_text_gin ON foo USING
gin((xpath('/root/element/text()', doc)::text[]));

To find documents which have <element>test</element>, using the above indexes:

# explain analyze select * from foo where
xpath_exists('/root/element[text()="test"]', doc);
 Bitmap Heap Scan on foo  (cost=3.33..450.22 rows=4311 width=36)
(actual time=0.025..0.026 rows=1 loops=1)
  Recheck Cond: xpath_exists('/root/element[text()="test"]'::text,
doc, '{}'::text[])
  ->  Bitmap Index Scan on foo_doc_id_exists_root_element_test
(cost=0.00..2.26 rows=4311 width=0) (actual time=0.014..0.014 rows=1
loops=1)
 Total runtime: 0.067 ms

# explain analyze select * from foo where
(xpath('/root/element/text()', doc)::text[]) @> array['test'];
 Bitmap Heap Scan on foo  (cost=8.50..105.51 rows=65 width=32) (actual
time=0.025..0.025 rows=1 loops=1)
  Recheck Cond: ((xpath('/root/element/text()'::text, doc,
'{}'::text[]))::text[] @> '{test}'::text[])
  ->  Bitmap Index Scan on foo_root_element_text_gin
(cost=0.00..8.49 rows=65 width=0) (actual time=0.020..0.020 rows=1
loops=1)
        Index Cond: ((xpath('/root/element/text()'::text, doc,
'{}'::text[]))::text[] @> '{test}'::text[])
 Total runtime: 0.046 ms
(5 rows)

The GIN index lets you search for documents that have both "test" and "testing":
(xpath('/root/element/text()', doc)::text[]) @> array['test','testing'];

Regards,
Marti