Omgo's Blog

May 24, 2010

postgres xml indexes

Filed under: postgres, sql — Tags: — aswin @ 11:48 pm

Just a simple example of the postgres xml indexing using functional indexes on xpath functions. If you have xml data that is used for read only and for searching, indexing the key search fields like this would get you good search performance and would avoid having to “shred” the data into individual columns.


-- create a table with a xml type column
CREATE TABLE testxml
(
  doc xml,
  id serial NOT NULL,
  CONSTRAINT testxml_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

-- insert some data
insert into testxml (doc) 
 select XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><id>'|| a || '</id><title>Manual</title><chapter>simple</chapter></book>') from generate_series(1, 1000) as s(a);

-- test if xpath is working fine
SELECT (xpath('/book/id/text()', doc))[1]::text from testxml;

-- check the explain plan , you should see a full table scan. not very efficient
explain SELECT * from testxml 
  where (xpath('/book/id/text()', doc))[1]::text = '100';

-- Add an index (function index) to the xpath. The result of the xpath would be indexed and the next time if this is used in predicate postgres 
-- would use this index instead of a full table scan
--drop index doc_idx
create  index doc_idx on testxml using btree (((xpath('/book/id/text()', doc))[1]::text)); 

--check it out in action
explain SELECT * from testxml 
  where (xpath('/book/id/text()', doc))[1]::text = '100';

References
http://developer.postgresql.org/pgdocs/postgres/functions-xml.html
http://developer.postgresql.org/pgdocs/postgres/datatype-xml.html
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

Blog at WordPress.com.