0

Postgresql has 4 types of index which are btree (default), hash, GIN and GIST. Which index type suit most for the following scenarios:

  1. search XML document via xpath. for example ((xpath('/book/id/text()', xml))[1]::text)
  2. Full text search over XML document
Improve this question
3
  • 2
    For 1) if that is always the same xpath, just create an index on that xpath expression. I am not aware of any index type that will allow arbitrary xpath expression to use an index. JSON/JSONB is more suited for that nowadays Commented Nov 21, 2016 at 11:52
  • 2
    For 2) FTS uses its own index. Use with caution though, it can be quite large. PostgreSQL wiki on XML indexing -- For 1) be aware that index on xpath expression may give no speed up at all Commented Nov 21, 2016 at 13:07
  • Pozs, thanks for the explaination. It seems like there is no one-size-fits-all solution when indexing postgresql XML data type. I should consider to convert the xml to json and store it in jsonb column. Commented Nov 22, 2016 at 4:40
Related questions
16
Postgres XML datatype
1
PostgreSQL 8.3 data types: xml vs varchar
34
How to determine what type of index to use in Postgres?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.