From: Denis Papathanasiou <denis(dot)papathanasiou(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject:
Date: 2013-02-15 17:55:50
Message-ID: CAEpnaGxYU+tmCzJUSbjOUdgLQJpqYV-4L9vM6+w1-1+HWj1N0g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm storing data in an xml type field, similar to what is explained in
this article: http://robots.thoughtbot.com/post/13829210385/the-durable-document-store-you-didnt-know-you-had-but

I'm also using the indexes he suggests for each xpath value; in my
case they look like this:

CREATE INDEX ind_entity_name ON forms
USING GIN ( CAST(xpath('//primaryEntity/entityName/text()', data) as TEXT[]) );

CREATE INDEX ind_prior_entity_name ON forms
USING GIN ( CAST(xpath('//primaryEntity/entityPreviousNameList/value/text()',
data) as TEXT[]) );

The first xpath -- '//primaryEntity/entityName/text()' -- has only one
possible value, so when I query it like this, the explain seems to
show that the index is being used (the forms' table has 7,085 rows):

mydb=> explain select id from forms where
(xpath('//primaryEntity/entityName/text()', data))[1]::text ~*
'^banc';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on forms (cost=0.00..770.27 rows=11 width=16)
Filter: (((xpath('//primaryEntity/entityName/text()'::text, data,
'{}'::text[]))[1])::text ~* '^banc'::text)
(2 rows)

But the second xpath --
'//primaryEntity/entityPreviousNameList/value/text()' -- points to
multiple possible values within the xml document, so to make sure that
I search them all, I have to write the query like this; in this case,
it seems the index is *not* being used and it's doing a full table
scan:

mydb=> explain select entity.id from (select id,
xpath(''//primaryEntity/entityPreviousNameList/value/text()', data),

generate_subscripts(xpath(''//primaryEntity/entityPreviousNameList/value/text()',
data), 1) as s1 from
forms) as entity where entity.xpath[s1]::text ~* '^banc';
QUERY PLAN
------------------------------------------------------------------
Subquery Scan on entity (cost=0.00..876.55 rows=11 width=16)
Filter: ((entity.xpath[entity.s1])::text ~* '^banc'::text)
-> Seq Scan on forms (cost=0.00..787.99 rows=7085 width=48)
(3 rows)

So is there a way to create an index which will help the performance
of this type of query?

Or is there a way of writing the second query differently that will
used the existing index better?

Browse pgsql-general by date

  From Date Subject
Next Message Ian Harding 2013-02-15 18:09:12 Re: pg_upgrade
Previous Message Ian Harding 2013-02-15 17:45:16 Re: pg_upgrade