Geek asks and answers

PostgreSQL is the fastest way to exclude a specific set of rows in all table searches

I am running a recipe website that uses PostgreSQL 9.1 as a backend. When a user searches for recipes, I create a query "on the fly" depending on what the user wants to find. For example, if a user wants to find all recipes that have a cooking time of less than 30 minutes, I would generate a query:

SELECT * From Recipes WHERE CookTime < 30;

Now I need to “hide” certain recipes, which means that they will never appear in any search. The only way to reach them is by knowing the URL directly. To do this, I added a new column to the recipe table:

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

My idea is to simply copy the phrase "NOT HIDDEN" into each WHERE clause. For example, the query above would be as follows:

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

My question is:

, . , 99% . , . , , , .

+3
3

? , , .

-, . , , .

, , , , , - , .

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

+2

- .

, - , , .

ALTER TABLE Recipes RENAME TO AllRecipes;
ALTER TABLE AllRecipes ADD Hidden BOOLEAN NOT NULL DEFAULT FALSE;
CREATE VIEW Recipes AS SELECT * FROM AllRecipes WHERE NOT Hidden;

, ( Recipies , ).

. Hidden. : VisibleRecipes HiddenRecipies. VisibleRecipies.

AllRecipies VisibleRecipes HiddenRecipes , .

+1

, .

, , CookTime 30, , hidden = true. , ( cooktime), .

But if your analyzer finds use of two indexes faster ...

Make sure you have statistics on the collected tables and indexes. (I have experience with Oracle, not Postgres)

0
source

More articles:


All Articles