Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Marti,
Thanks, this is exactly the kind of feedback I was looking for.
I am already storing the whole XML in a payload table actually. My problem is, the queries are actually created in a domain specific langauge, and then they are transformed to SQL.
There is a no way of knowing what kind of queries would be run over the XML docs, so I'd be creating indices over and over with each incoming new query. Still, I may be able to find a path in the middle, maybe using a combination of the XML path based index and row based representation. Thaks for the pointers to relevant index types.

Finally, ltree may be an alternative to xpath based indices, but I don't know if that would be faster. The database may need to go beyond 100 milion rows, and I'm not sure what would happen then with a row based representation. I'll probably generate dummy data and compare performance of the options.

Kind regards
Seref


On Fri, May 18, 2012 at 10:55 AM, Marti Raudsepp <marti@xxxxxxxxx> wrote:
On Thu, May 17, 2012 at 8:40 PM, Seref Arikan <serefarikan@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux