Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
>> why would you store data thats wrapped in two copies of its fieldname along
>> with other punctuation? wouldn't it make more sense to decompose your XML
>> source into proper tables so proper indexes and relational sql queries can
>> be made? otherwise, every query turns into a massive sequential scan and
>> parsing operation.
> you can always have index on xpath() ...
> and than use same expression in WHERE, and postgresql will use index.
Interesting. I had thought that it was not possible to index
on XML columns because no comparison operators were defined:
| tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY);
| FEHLER: Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree«
| HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp
definieren.
yet an array of XML works:
| tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY);
| HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml«
| CREATE TABLE
| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml
version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')));
| INSERT 0 1
though only once:
| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml
version="1.0"?><book><title>Manual2</title><chapter>...</chapter></book>')));
| FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln
So an index on xpath() should not be possible.
Tim
