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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: