Take the 2-minute tour ×

I run a recipe website that uses PostgreSQL 9.1 as a backend. When a user searches for recipes, I build a query on the fly depending on what the user wants to find. For example, if the user wants to find all recipes that have a cook time under 30 minutes, I would generate the query:

SELECT * From Recipes WHERE CookTime < 30;

I now have the need to "hide" certain recipes, meaning they will never show up in any search, ever. The only way to get to them would be knowing the URL directly. To do this, I've added a new column to the Recipes table:

ALTER TABLE Recipes ADD COLUMN Hidden boolean not null default false;
CREATE INDEX IDX_Recipes_Hidden ON Recipes(Hidden);

My idea is to just hard code the phrase "NOT HIDDEN" into every WHERE clause. For example, the query above would now be:

select * from recipes where not Hidden and CookTime < 30;

My Question:

According to the query analyzer, this will now generate a bitmap to combine the two indexes. Keep in mind 99% of the recipes will not be hidden. I want to know if this technique is the best, and fastest way to exclude certain recipes from all queries. I know the absolute fastest way would be to create a separate table for hidden recipes, however this would be a massive amount of re-factoring so I'd like to avoid this.

share|improve this question
Why you think «massive amount of re-factoring» will be required? You can hide your storage implementation behind a view with a set of rules. This is how the partitioning works in PostgreSQL> –  vyegorov Apr 19 '12 at 6:46
"Keep in mind 99% of the recipes will not be hidden" Don't index this field, just a waist of time and space. Unless you use a conditional index: WHERE hidden = true –  Frank Heikens Apr 19 '12 at 8:06
It appears we had the same (kind of) question here: stackoverflow.com/a/8544893/905902 . Anyway, I agree with @Frank Heikens; the index will probabaly make no sense (except in the case where all of the database is kept in memory, and even more memory is available) –  wildplasser Apr 19 '12 at 8:47
@FrankHeikens - Good call on the conditional index. This would make the index incredibly small, and then it would just scan this index to remove hidden recipes from the query. I'll do some testing on this, and also just not having the index at all and see what works best. –  Mike Christensen Apr 19 '12 at 15:13
@vyegorov - Yea, I suppose there are ways to avoid a lot of the re-factoring with views and rules, but I'd see no perf benefit unless I was actually using two recipe tables under the covers, which I'd like to avoid if I can for now. –  Mike Christensen Apr 19 '12 at 15:22
add comment

3 Answers

up vote 2 down vote accepted

Do you have any performance issues? If there are no issues with your solution it makes no sense to waste more time on something that needs no change.

A bitmap index is fine for something where you have not many different values. So in your case where you only have true and false it is fine.

You could just build something like a materialized view but this seems to be to much work and it would be probably easier for you to just create a second table, but if you do not have any issues don't change anything.

MVs in postgres: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

share|improve this answer
Yea, that'd be hugely overkill. I'm wondering if I'd be better off creating multicolumn indexes on everything, but I think most queries will end up doing BitmapAnd's anyway. As you said, why bother if I don't have perf issues. Half of this question is posted for my own intellectual development - I'm not a DB expert, but I want to learn more. –  Mike Christensen Apr 19 '12 at 4:30
Arbitrarily adding indexes does not necessarily improve performance. Sometimes the use of an index could wreak havoc on a query and cause performance to to do worse than a full table scan due to paging issues, IIRC. –  JayC Apr 19 '12 at 4:38
I've decided to keep my current implementation, however make the index on Hidden conditional since I will never be scanning for rows that are hidden. Down the road if I do run into perf issues, I will look into partitioning as you've mentioned. Thanks! –  Mike Christensen Apr 19 '12 at 15:25
Adding WHERE NOT hidden to your index definition is probably plenty good, based on everything you said here. Possibly a VIEW for use by your web application so you don't need to always work it into the query conditions, if you find that to be a hassle. –  kgrittn May 8 '12 at 19:49
add comment

The fastest way to stop rows from showing up ever again is... delete them.

But if you want them round for some purpose, but don't want them for almost all queries, you could rename the table and create a new view in its place.


This is fastest in terms of how much code you will need to rewrite (assuming you have many queries on your app on Recipies, and want all of them to exclude the hidden ones).

But it also gives you easy options to make it fast for performance too. For a start you can add an index on Hidden. But you can also partition it into two subtables, VisibleRecipes and HiddenRecipies. The view Recipes will show exactly the ones in VisibleRecipies.

But the table AllRecipies could be either a parent table with VisibleRecipes and HiddenRecipes as its partitions, or it could be a view itself.

share|improve this answer
+1 for partitoning ideea. But because they are 1%, will not be a big deal to filter them. –  Florin Ghita Apr 19 '12 at 6:33
Not really sure on the point of the view, unless it's materialized (which Postgres doesn't support). The view will just show rows from AllRecipes where Hidden is false - how is that different from just adding that clause directly to my query when I search? Or perhaps your point was that this puts me in a better position down the road if I do want to partition the tables. –  Mike Christensen Apr 19 '12 at 15:17
add comment

If you don't have performance issues is ok.

If I was the engine, I would use the index to get the table rows with CookTime lesser than 30, and after this I would filter those with hidden = true. If you know how to enforce this(use of cooktime index only), is fine to test it.

But if your analyser find usage of two indexes faster...

Be sure you have statistics on the tables and indexes collected. (I have expertise on Oracle, not Postgres)

share|improve this answer
Yea, from my tests so far, Postgres will use both indexes and create a BitmapAnd. It's possible that it didn't have up to date stats, since I just created the index, so maybe in certain cases it won't even touch the Hidden index. –  Mike Christensen Apr 19 '12 at 15:18
add comment

Your Answer


