Tino Wildenhain wrote:
Craig A. James schrieb:
...
In our case (for a variety of reasons, but this one is critical), we actually can't use Postgres indexing at all -- we wrote an entirely separate indexing system for our data...

...There is no need to store or
maintain this information along with postgres when you can store
and maintain it directly in postgres as well.

Whether we store our data inside or outside Postgres misses the point (in fact, 
most of our data is stored IN Postgres).  It's the code that actually performs 
the index operation that has to be external to Postgres.

On top of that, postgres has a very flexible and extensible index
system.

You guys can correct me if I'm wrong, but the key feature that's missing from 
Postgres's flexible indexing is the ability to maintain state across queries.  
Something like this:

 select a, b, my_index_state() from foo where ...
   offset 100 limit 10 using my_index(prev_my_index_state);

The my_index_state() function would issue something like a "cookie", an opaque 
text or binary object that would record information about how it got from row 1 through 
row 99.  When you issue the query above, it could start looking for row 100 WITHOUT 
reexamining rows 1-99.

This could be tricky in a OLTP environment, where the "cookie" could be 
invalidated by changes to the database.  But in warehouse read-mostly or read-only 
environments, it could yield vastly improved performance for database web applications.

If I'm not mistaken, Postgres (nor Oracle, MySQL or other RDBMS) can't do this. 
 I would love to be corrected.

The problem is that relational databases were invented before the web and its stateless 
applications.  In the "good old days", you could connect to a database and work 
for hours, and in that environment cursors and such work well -- the RDBMS maintains the 
internal state of the indexing system.  But in a web environment, state information is 
very difficult to maintain.  There are all sorts of systems that try (Enterprise Java 
Beans, for example), but they're very complex.

Craig


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to