KNN indexing!
Can anyone give an example use case for this? I'm not sure I fully understand what it means.
There's this Accelerating Local Search talk this Friday (Sep-16).
PostgreSQL 9.1 released
41–47 of 47 posts
Re: PostgreSQL 9.1 released
#42Earlier quoted context omitted.
A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS. The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as g…
I think that's probably true, though I do wonder from a purely engineering standpoint what features Oracle/DB2/MS have at this point that PostgreSQL does not. Special index types? Query hints? Suggestions for physical layout on table creation?
More advanced query plan generators in commercial DBs.
On other side:
1. PostgreSQL and MySQL are catching up (for example: Table elimination)
2. You can always throw saved money in hardware. 40 thousands per core (Oracle) - is ridiculous.
Re: PostgreSQL 9.1 released
#43Earlier quoted context omitted.
A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS. The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as g…
I think that's probably true, though I do wonder from a purely engineering standpoint what features Oracle/DB2/MS have at this point that PostgreSQL does not. Special index types? Query hints? Suggestions for physical layout on table creation?
[edited for formatting]
Speaking only to the differences between PostgreSQL and SQL Server:
1. "Live" clustered indexes
2. Query Parallelism
3. A richer API for data partitioning and _arguably_ a better query optimizer for partitioned data. 9.1 sees some wonderful improvements to the optimizer in this regard and I'll be poking at those very features in the coming months.
SQL Server has query hints, but I strongly agree with the PostgreSQL's team stance on them; I'm glad they avoid them.
As far as special index types: If you need a specialized index type, and nobody supports it, you're dramatically, drastically, fantastically more likely to implement it/find someone to implement it in PostgreSQL than any commercial engine.
Re: PostgreSQL 9.1 released
#44Earlier quoted context omitted.
It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?
Because I spent $60 on an awesome MySQL reference guide and tutorial. http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...
Please don't pimp your referral links on HN
Re: PostgreSQL 9.1 released
#45Earlier quoted context omitted.
Nice, thanks for all the examples and explanations! Anyone know if it's possible to define a custom distance metric for use with this? We don't currently use full-text or spatial indices, but I can think of some cool things we could do with a generalised notion of "distance".
Yes, it is possible. Postgres supports custom data types and operators on those data types, and (using the GiST subsystem) you can create indexes using those custom operators. Unfortunately, there is a caveat: this will more than likely require writing custom C code. I've never tried to implement GiST indexing for a new data type, so I don't know just how much effort would be required. What kind of distances did you…
We have a table which essentially represents a shallow graph (rows in the table can have links to to one or more other rows in the table). It would probably be interesting if we could define a distance metric in terms of those links: "find all rows reachable in 3 links from this starting row".
This probably isn't ideally done by this sort of index, as it would be a pretty weird sort of distance metric, if it's even a valid one (most rows aren't connected, meaning the "distance" between most rows would be infinite, and our query would really be "WHERE distance < infinity"). But it's good to know there's some kind of support for it!
Re: PostgreSQL 9.1 released
#46Earlier quoted context omitted.
Yes, it is possible. Postgres supports custom data types and operators on those data types, and (using the GiST subsystem) you can create indexes using those custom operators. Unfortunately, there is a caveat: this will more than likely require writing custom C code. I've never tried to implement GiST indexing for a new data type, so I don't know just how much effort would be required. What kind of distances did you…
We have a table which essentially represents a shallow graph (rows in the table can have links to to one or more other rows in the table). It would probably be interesting if we could define a distance metric in terms of those links: "find all rows reachable in 3 links from this starting row". This probably isn't ideally done by this sort of index, as it would be a pretty weird sort of distance metric, if it's even a…
Ah, right. For the kNN/GiST support in Postgres, distance has to be defined in such a way that it is computable given only two data values; furthermore, I'm pretty sure it has to be both symmetric and satisfy the triangle inequality in order for the index to produce correct results.
Re: PostgreSQL 9.1 released
#47Earlier quoted context omitted.
Because I spent $60 on an awesome MySQL reference guide and tutorial. http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...
Please don't pimp your referral links on HN
I didn't know linking to Amazon did anything other than link to a page. I added this to make a point in the discussion about training and investment.
Thanks for helping me and being so polite about it.