Rudder

We could also create a index on the xpath query
See
http://www.postgresql.org/message-id/29360.1267795352@sss.pgh.pa.us
http://stackoverflow.com/questions/31589652/postgresql-xpath-integer-index

I did duplicate the data "eventlog" (27G Table when dumped, 1.7G compressed) and did some tests on it.

Query without the index:

# time psql -d rudder -c "select count(*) from (select xpath('/entry/*[@fileFormat=4]',data) AS x from eventlog) as Y where array_upper(x, 1) > 0;" 
 count
-------
     0
(1 row)

real    5m1.342s
user    0m0.000s
sys     0m0.000s

Added an Index like this:

# time psql -d rudder -c "CREATE INDEX eventlog_fileFormat ON eventlog (((((xpath('/entry/*/@fileFormat',data))[1])::text)::integer))" 
CREATE INDEX

real    5m1.029s
user    0m0.000s
sys     0m0.000s

Re-Run the same query:

# time psql -d rudder -c "select count(*) from (select xpath('/entry/*[@fileFormat=4]',data) AS x from eventlog) as Y where array_upper(x, 1) > 0;" 
 count
-------
     0
(1 row)

real    5m1.093s
user    0m0.000s
sys     0m0.000s

Basically no change, you'd have to change the original query, since it queries the whole same dataset for each fileFormat version twice, it would be a lot better to query all distinct values found as fileFormats, and do decisions according to which versions have been found...

Hum, if there's no change in the query time, then the index is not used.
Index looks correct, so maybe the query isn't

What actually would help (without actually improving the original query's speed) is to do something like this:

# time psql -d rudder -c "select distinct Format from (select ((xpath('/entry//@fileFormat',data))[1]::text) as Format from eventlog) AS foo;" 
 format
--------

 5
 6
(3 rows)

real    5m13.226s
user    0m0.000s
sys     0m0.000s

This would only parse all the table content once, then you could make decisions based on what results came back.

As discussed on IRC:

New query without an Index in place (still long):

# time psql -d rudder -c "select count(*) from (select ((xpath('/entry//@fileFormat',data))[1]::text) as id from eventlog) as T where id='5'" 
 count
--------
 138272
(1 row)

real    5m7.942s
user    0m0.000s
sys     0m0.000s

Putting the index in place:

# time psql -d rudder -c "CREATE INDEX eventlog_fileFormat ON eventlog (((((xpath('/entry//@fileFormat',data))[1])::text)));" 
CREATE INDEX

real    5m9.031s
user    0m0.000s
sys     0m0.000s

Now the query runs like a charm in basically not time:

# time psql -d rudder -c "select count(*) from (select ((xpath('/entry//@fileFormat',data))[1]::text) as id from eventlog) as T where id='5'" 
 count
--------
 138272
(1 row)

real    0m0.093s
user    0m0.000s
sys     0m0.000s