Optimizing SQL query with checking special rules set

Question

(First of all sorry for my English. I am not native speaker)

I have a problem with selecting data.

My problem is that I don't know how to optimize SELECT from table "component". As I understand – I cannot create any indexes which help Postgres make select based on set of rules from other table.

I have following schema: Component can have several parameters and one owner. Parameters and owners can be added to any groups. There is several conveyors where this components should appear. There is special filtration rules, which based on component's groups and owners. This rule can allow component or block it. Rules may be any number and some additional logic can be applied for them.

Table “component” can contains large amount of data. Each conveyor should process components by portions (or by pages).

For know I am doing so: 1.Take conveyor id and select all it's rules into the temporary table. 2.Go through all components and call my function for each which takes component_id and owner_id and use temporary table from step 1 for checking rules.

look like this:

SELECT *
FROM component
WHERE check_rules(component_id, owner_id)
LIMIT p_limit OFFSET p_offset

But in this case Postgres every time should go through all records in table which makes performance hit. This structure should work in high load environment with large amount of data. So I need way to optimize this situation or have ability to scale it. Probably using Postgres in this case is not good solution and I need use some other system (like MapReduce or BigTable, but for know I am not work with them).

Recommend something please. Thanks a lot.

Here is some sql code if I am told it is not clear.

CREATE TABLE component(
    component_id int,
    component_name varchar,
    owner_id int);

CREATE TABLE parameter(
    parameter_id int,
    name varchar);

–component can have several parameters
CREATE TABLE component_parameter(
    component_id int,
    parameter_id int);

--tables for grouping parameters and owners 
CREATE TABLE parameter_group(
    group_id int,
    parameter_id int);

CREATE TABLE owner_group(
    group_id int,
    owner_id int)

CREATE TABLE conveyor(
    conveyor_id int,
    conveyor_name varchar)

CREATE TABLE conveyor_rule(
    conveyor_id int,
    rule_id int,
    allow_work boolean)

CREATE TABLE conveyor_rule_item(
    rule_id int,
    parameter_group_id int,
    owner_group_id int)

Answer 1

I would suggest to get rid of both the function in the WHERE-statement and the use of temporary tables. Instead of them, try to use INNER JOINS. If this is not possible try at least to take one of them out, for example by creating a temporary table and join against that or create a function that does not use a temporary table.