Re: XML index support

Dear friends,

You may know me as I worked a long time ago on pgAdmin I with Dave.

As this is an XML related question and XML is quite new, I am posting
on hackers ML. If any solution is being developped, please inform us.

We would like to develop a free REST database (real-estate standard)
based on a PostgreSQL schema. This is a free solution for free data
also, released under BSD or GPL license. We are charity.

This is supposed to a "killer application", so we need to drive down
queries to 3ms to 5ms to allow hundreds of simultaneous queries.

Php will probably be part of the package.

What is in your opinion the best way to achive this :
* develop a traditional databe and implement materialized views in PL
OR
* implement the new XML type with some index (GIST) designed for
PostgreSQL. Which one?
OR
* write triggers behind XML tables to pullulate a traditionnal database,
add indexes and query traditionnal database.

Kind regards,
Jean-Michel

Jean-Michel,

> As this is an XML related question and XML is quite new, I am posting
> on hackers ML. If any solution is being developped, please inform us.

How is it XML-related?

> We would like to develop a free REST database (real-estate standard)
> based on a PostgreSQL schema. This is a free solution for free data
> also, released under BSD or GPL license. We are charity.
>
> This is supposed to a "killer application", so we need to drive down
> queries to 3ms to 5ms to allow hundreds of simultaneous queries.

Well, anything based on XML data is going to be pretty slow. There's just no
way to extra data from an XML field without lots of parsing. I'd tend to
think it would be much faster to store the data conventially, and just
generate XML in response to requests ... either inside or outside PostgreSQL.

Of course, I'd need a lot more detail to make a serious assessment.

Mind you, we'd be thrilled to have you implement a special XML index type. I
don't think it's going to solve your problem, though.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Well, anything based on XML data is going to be pretty slow. There's just no
> way to extra data from an XML field without lots of parsing.

I thought there'd been some discussion of storing XML data values in
some kind of pre-parsed format?

I agree that's got about nothing to do with indexing, though.

regards, tom lane

On Fri, 27 Jun 2008, Jean-Michel Pour? wrote:

> Dear friends,
>
> You may know me as I worked a long time ago on pgAdmin I with Dave.
>
> ЪЪAs this is an XML related question and XML is quite new, I am posting
> on hackers ML. If any solution is being developped, please inform us.
>
> We would like to develop a free REST database (real-estate standard)
> based on a PostgreSQL schema. This is a free solution for free data
> also, released under BSD or GPL license. We are charity.
>
> This is supposed to a "killer application", so we need to drive down
> queries to 3ms to 5ms to allow hundreds of simultaneous queries.
>
> Php will probably be part of the package.
>
> What is in your opinion the best way to achive this :
> * develop a traditional databe and implement materialized views in PL
> OR
> * implement the new XML type with some index (GIST) designed for
> PostgreSQL. Which one?
> OR
> * write triggers behind XML tables to pullulate a traditionnal database,
> add indexes and query traditionnal database.

Hmm, why do you need XML here ? I'd use standard relational
approach with our contrib/hstore module for storing specific content,
which is a probable reason you want XML. Table inheritance would be also
useful.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Thank you all for these kind answers. Cheers, JMP