Postgresql 9.x: Index to optimize `xpath_exists` (XMLEXISTS) queries

Question

We have queries of the form ``` select sum(acol) where xpath_exists('/Root/KeyValue[Key="val"]/Value//text()', xmlcol) ``` What index can be built to speed up the where clause ? A btree index created using ``` create index idx_01 using btree(xpath_exists('/Root/KeyValue[Key="val"]/Value//text()', xmlcol)) ``` does not seem to be used at all. EDIT Setting `enable_seqscan` to `off`, the query using `xpath_exists` is much faster (one order of magnitude) and clearly shows using the corresponding index (the btree index built with `xpath_exists`). Any clue why PostgreSQL would not be using the index and attempt a much slower sequential scan ? Since I do not want to disable sequential scanning globally, I am back to square one and I am happily welcoming suggestions. EDIT 2 - Explain plans See below - Cost of first plan (seqscan off) is slightly higher but processing time much faster ``` b2box=# set enable_seqscan=off; SET b2box=# explain analyze Select count(*) from B2HEAD.item where cluster…

Accepted Answer

Planner cost parameters

Cost of first plan (seqscan off) is slightly higher but processing time much faster

This tells me that your `random_page_cost` and `seq_page_cost` are probably wrong. You're likely on storage with fast random I/O - either because most of the database is cached in RAM or because you're using SSD, SAN with cache, or other storage where random I/O is inherently fast.

Try:

```
SET random_page_cost = 1;
SET seq_page_cost = 1.1;
```

to greatly reduce the cost param differences and then re-run. If that does the job consider changing those params in `postgresql.conf.`.

Your row-count estimates are reasonable, so it doesn't look like a planner mis-estimation problem or a problem with bad table statistics.

Incorrect query

Your query is also incorrect. `OFFSET 0 LIMIT 1` without an `ORDER BY` will produce unpredictable results unless you're guaranteed to have exactly one match, in which case the `OFFSET ... LIMIT ...` clauses are unnecessary and can be removed entirely.

You're usually much better off phrasing such queries as `SELECT max(...)` or `SELECT min(...)` where possible; PostgreSQL will tend to be able to use an index to just pluck off the desired value without doing an expensive table scan or an index scan and sort.

Tips

BTW, for future questions the PostgreSQL wiki has some good information in the performance category and a guide to asking Slow query questions.

Postgresql 9.x: Index to optimize `xpath_exists` (XMLEXISTS) queries

Solution

Problem