Postgresql – How simplify/optimize a search of keywords in two tables at the same time

functionsperformanceplpgsqlpostgresqlpostgresql-9.4query-performance

I'm trying to model a function which will be used by a search engine (of cars for the example).
The last version of PostgreSQL will be installed on a Windows server (I don't know server's specifications for the moment). The default Postgresql configuration will be used.
In this search engine, you can type many words and each word can correspond to a specific car part or a tag.
The function below seems to work but i have some doubts about its performance on the long term because the 'tag' table and the 'parts' table will constantly grow. We can consider that each table will have more than a million of row.
The fields cars.id, parts.id, cars_parts, cars_tags.id are declared as primary keys so there is an index on each of them.
Each item in "tags" table contain only one word. Items in "parts" can contain many word.
How can I improve simplify it and improve its performance ?

CREATE OR REPLACE FUNCTION search(keywords text[], itemsLimit int)
    RETURNS TABLE(nbr bigint, cars_id bigint, cars_name varchar, cars_description text) AS $$
DECLARE
    potential_parts text[];
    potential_tags text[]; 
    keyword text;
BEGIN
    FOREACH keyword IN ARRAY keywords
    LOOP
    potential_parts := array_append(potential_parts, '%' || keyword || '%');
    potential_tags := array_append(potential_tags, keyword || '%');
    END LOOP;

    RETURN QUERY
    SELECT COUNT(*) nb, id, name, description
    FROM (
        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_parts CP ON cars.id = CP.car_id
        INNER JOIN parts P ON CP.part_id = P.id
        WHERE p.name ILIKE ANY(potential_parts)

        /* Duplicates required to get cars having (part + tag) on top,
         using an order by on the count */
        UNION ALL

        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_tags CT ON cars.id = CT.car_id
        INNER JOIN tags T ON CT.tag_id = T.id
        WHERE T.name ILIKE ANY(potential_tags)
    ) ids
    GROUP BY id, name, description
    ORDER BY nb DESC
    LIMIT itemsLimit;
END;
$$ LANGUAGE plpgsql;

-- Example of call :
    SELECT * FROM search(array['Fast', 'Wheel-554D'], 10);

Best Answer

The default Postgresql configuration will be used.

The default PostgreSQL configuration is meant to be adequate for smallish servers. If you are worried about scalability, which you seem to be, then restricting yourself to using the default configuration is going to be a poor plan.

WHERE p.name ILIKE ANY(potential_parts)

You are probably going to want a trgm_ops index on p.name, and similarly on T.name

You might also need to add user-space code which prevents people from searching on things of less than 3 or 4 characters.

We can consider that each table will have more than a million of row.

Make a million rows of dummy data, and see what happens. No use speculating when you can just do it.

Better index

Your index idx_pd_order is not a big help, since filtering on non-leading columns of a multi-column index is not very efficient. Postgres is using it - still a lot better than a sequential scan. Details here:
Is a composite index also good for queries on the first field?

idx_pd_where might be a better choice, but not a good one, either. The leading column status_id is not selective at all and just bloats the index. Same goes for the trailing column is_active. And priority is not in the index and has to be fetched from the table, making an index-only scan impossible.

I suggest this partial, multi-column index to start with. (But keep reading!)

CREATE INDEX idx_pd_covering ON tb_pd (next_execution_date, priority, st_id)
WHERE  status_id = 1 AND is_active = 1

Since we are only ever interested in rows with status_id = 1and is_active = 1 exclude other rows from the index right away. Size does matter.
The remaining (crucial) condition is on next_execution_date, which must come first in the index.
priority and st_id are only appended for a possible index-only scan (Postgres 9.2+). If that doesn't take, remove the columns from the index to make is smaller.

Special difficulty

We can now use idx_pd_covering to find qualifying rows quickly, unfortunately we have to look at all qualifying rows to collect the ones with highest priority. As the query plan reveals, Postgres estimates to process 34627017 rows. Sorting 35M rows is going to cost big. That's the tricky part I mentioned at the start. To demonstrate what I am talking about, run EXPLAIN on your query with and without priority in ORDER BY:

SELECT s.st                                               
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY priority, next_execution_date
LIMIT  20000;

That's your query, formatted only slightly simplified. You should see a huge difference.

Solution

The solution depends on the number of distinct values for priority. For lack of information and for demo purposes I am going to assume only three. Priority 1, 2 and 3.

With a trivial number of distinct priority values, there is a simple solution. Create three partial indexes. All of them together are still smaller than your current indexes idx_pd_order or idx_pd_where (which you might not be needing any more).

CREATE INDEX idx_pd_covering_p1 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 1 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p2 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 2 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p3 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 3 AND status_id = 1 AND is_active = 1;

Use this query:

SELECT s.st
FROM  (
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 1
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 2
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   ...
   AND    priority  = 3
   ...
   )
   LIMIT  20000
   ) p
JOIN   tb_st s USING (st_id);

This should be dynamite.

Strictly speaking, the final order is not guaranteed without an additional ORDER BY clause in the outer query. In the current implementation, the order from the inner query is preserved as long as the outer query is as simple as that. To be sure, you could join right away (which may be a bit slower):

)
SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.priority  = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY p.next_execution_date
)
UNION ALL
(
...
)
LIMIT  20000;

.. or carry along priority and next_execution_date to order once more in the outer query (to be absolutely sure), which is probably slower, yet.

All parentheses are needed! Related answer.
This query just reads tuples from the top of above partial indexes, no sort-step needed at all. All rows are pre-sorted, efficient to boot.
UNION ALL queries without a final ORDER BY can stop as soon the number of requested rows in the top-level LIMIT have been fetched. So if there are enough rows in the top priorities, subsequent legs of the UNION ALL query are never executed. This way, only the smaller partial indexes have to be touched.
JOIN to tb_st later, should be more efficient.
Again, the column st_id is only appended to the index in the hope for an index-only scan. If that works for you, the whole query does not even touch the table tb_pd at all.

General solution for any number of distinct `priority` values

We have solved this before. There is a complete recipe to automate the creation of partial indexes and a function .. the works:
Can spatial index help a "range - order by - limit" query

Optimize table

Since you are trying to optimize performance and your table is big, I suggest a slightly altered layout for your table tb_pd:

   Column            |  Type
---------------------+--------
 pd_id               | integer
 st_id               | integer
 next_execution_date | bigint
 priority            | integer  -- or smallint? -- or "char"?
 status_id           | smallint -- or "char"
 is_active           | boolean

This occupies 52 bytes per row on disk, while your current design needs 60 bytes. Indexes profit as well. Details:
Configuring PostgreSQL for read performance

Of course, all the basic advice for performance-optimization applies as well.

About "char":

The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

Mysql – Optimizing a query that returns several avg() values

This is an alternative scheme to the ones already proposed, it's a bit like the summary table, except that it isn't aggregated, just 'partitioned' by player.

Create Table gamelogs_by_player (
    player_id bigint(20) Not Null,
    home_or_away char(1) Not Null, 
    `date` int(11) Not Null,
    game_id int(11) Not Null,
    ab tinyint(3),
    h tinyint(3),
    2b,
    3b,
    hr,
    rbi,
    ...,
    Constraint pk_gamelogs_by_player Primary Key Clustered (player_id, home_or_away, `date`);

Then, once for each player for home games (and likewise for away games):

Insert Into gamelogs_by_player (...)
Select var1 As player_id,
    'H' As home_or_away,
    `date`,
    game_id,
    h_ab,
    h_2b,
    ...
  From gamelogs
  Where h_1_id = var1
     Or h_2_id = var1
     Or ...

This table will have more rows than your original table, but each time you query the table should be faster because the query can go straight to the correct rows rather than reading the whole table and evaluating the filter each time.

Best Answer

Related Solutions

PostgreSQL Query Optimization – How to Optimize a Query on Two Big Tables