In this, hopefully 2nd to last, post in the series, I will cover the rest of usually happening operations that you can see in your explain outputs.
On 3rd of November, Heikki Linnakangas committed patch:
Support range data types. Selectivity estimation functions are missing for some range type operators, which is a TODO. Jeff Davis
Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.
select a.* from articles as a join articles_in_categories as aic on a.id = aic.article_id where aic.category_id in (14,62,70,53,138)
Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?
On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:
Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at the time of insertion. This works well as long as there aren't many conflicts, but won't scale to massive unique-key reassignments. Improving that case is a TODO item. Dean Rasheed
Every so often you need to get list of unique elements in some column. The standard way to do it is:
select distinct column from table;
select column from table group by column;
The only problem is that it's slow – as it has to seq scan whole table. Can it be done faster?