Query optimization on large PostgreSQL tables

I’m encountering performance issues with a PostgreSQL query that retrieves point data from a table containing around 6 million records, which will eventually grow to billions. The query, which returns approximately half a million records, executes in 639 ms according to EXPLAIN ANALYZE. However, it takes around 45 seconds to actually retrieve and display all the rows in pgAdmin.

The points table contains spatial data (points) alongside other attributes, and I’m joining it with an ids table to filter the results based on specific criteria. Despite the indexes I built, I’m still struggling to reduce the query execution time. Below is a standard query that is run between my points and ids table.

Query:

  SELECT 
        p."ID", 
        p."projectCode", 
        p."dateTime", 
        ST_AsText(p."geom") AS "geom",  
        i."notes",
        i."name"

    FROM 
        public.points AS p
    INNER JOIN 
        public.ids AS i
        ON p."ID" = i."ID"
        AND p."projectCode" = i."projectCode"
    WHERE 
        p."projectCode" = 'F2001' -- ProjectCode is a commonly used filter but also will be filtering by "name", "ID", or other columns in my ids table. 
        AND p."dateTime" >= i."startDate"
        AND p."dateTime" <= i."endDate"
        AND p."includePoint" = TRUE;

Query Result:

| **ID** | **projectCode** | **dateTime**        | **geom**                             | **Notes** |
|--------|-----------------|---------------------|--------------------------------------|-----------|
| 1      | F2001           | 12/14/2012 18:00    | POINT(-107.195691 41.710202)         | Example   |
| 1      | F2001           | 12/15/2012 0:00     | POINT(-107.196883 41.7107840000001)  | Example   |

Explain Analyze Output:

Gather  (cost=1259.68..74572.09 rows=17353 width=539) (actual time=5.204..616.609 rows=631960 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Join  (cost=259.68..71836.79 rows=7230 width=539) (actual time=4.797..342.388 rows=210653 loops=3)
        Hash Cond: (((p."ID")::bpchar = i."ID") AND ((p."projectCode")::bpchar = i."projectCode"))
        Join Filter: ((p."dateTime" >= i."startDate") AND (p."dateTime" <= i."endDate"))
        Rows Removed by Join Filter: 15681
        ->  Parallel Index Scan using idx_points_composite on points p  (cost=0.56..62631.40 rows=276115 width=54) (actual time=0.041..65.279 rows=226335 loops=3)
              Index Cond: ((("projectCode")::text = 'F2001'::text) AND ("includePoint" = true))
        ->  Hash  (cost=235.05..235.05 rows=1605 width=603) (actual time=4.676..4.677 rows=1605 loops=3)
              Buckets: 2048  Batches: 1  Memory Usage: 669kB
              ->  Seq Scan on ids i  (cost=0.00..235.05 rows=1605 width=603) (actual time=0.014..1.698 rows=1605 loops=3)
Planning Time: 1.195 ms
Execution Time: 639.822 ms

What I’ve Tried:

I’ve added a composite index on projectCode, ID, dateTime, and includePoint on the points table and I have an index on my ids table on the columns ID, projectCode, startDate and endDate. I’ve also ran VACUUM on the points table.

Are there additional optimizations I could apply to reduce the execution time? I’m uncertain if partitioning by year is necessary at this stage, given the size of the table.

Points table information:

   | **Column Name** | **Data Type**               | **Length** | **Nullable** | **Constraint** | **Index Name**          | **Index Definition**                                                                                                                                       |
|-----------------|-----------------------------|------------|--------------|----------------|-------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------|
| guid            | integer                     |            | NO           | PRIMARY KEY    | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| guid            | integer                     |            | NO           | PRIMARY KEY    | points_pkey             | CREATE UNIQUE INDEX points_pkey ON public.points USING btree (guid)                                                                                        |
| geom            | USER-DEFINED                |            | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| ID              | character varying           | 50         | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| projectCode     | character varying           | 50         | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| dateTime        | timestamp without time zone |            | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| includePoint    | boolean                     |            | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| dimension       | character varying           | 50         | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| hdop            | double precision            |            | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| activity        | character varying           | 255        | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")                                           |
| numsat          | integer                     |            | YES          |                | idx_points_composite    | CREATE INDEX idx_points_composite ON public.points USING btree ("projectCode", "ID", "dateTime", "includePoint")

UPDATE

Based on the suggestions below, I created two new indexes on my points table. Including dateTime in the ON clause significantly increased query time, so I opted to include it only in the INCLUDE clause:

   CREATE INDEX idx_points_001a ON public.points ("projectCode", "includePoint") INCLUDE ("geom", "dateTime");
    CREATE INDEX idx_points_001b ON public.points ("ID", "includePoint") INCLUDE ("geom", "dateTime");

Gather  (cost=1259.68..90237.64 rows=25279 width=539) (actual time=3.854..470.662 rows=404208 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=28276
  ->  Hash Join  (cost=259.68..86709.74 rows=10533 width=539) (actual time=3.833..282.719 rows=134736 loops=3)
        Hash Cond: (((p."ID")::bpchar = i."ID") AND ((p."projectCode")::bpchar = i."projectCode"))
        Join Filter: ((p."dateTime" >= i."startDate") AND (p."dateTime" <= i."endDate"))
        Rows Removed by Join Filter: 1348
        Buffers: shared hit=28276
        ->  Parallel Index Scan using idx_points_001a on points p  (cost=0.56..73416.64 rows=402311 width=54) (actual time=0.028..87.204 rows=317173 loops=3)
              Index Cond: ((("projectCode")::text = 'F2001'::text) AND ("includePoint" = true))
              Buffers: shared hit=27559
        ->  Hash  (cost=235.05..235.05 rows=1605 width=603) (actual time=3.728..3.729 rows=1605 loops=3)
              Buckets: 2048  Batches: 1  Memory Usage: 669kB
              Buffers: shared hit=657
              ->  Seq Scan on ids i  (cost=0.00..235.05 rows=1605 width=603) (actual time=0.014..1.359 rows=1605 loops=3)
                    Buffers: shared hit=657
Settings: effective_cache_size = '12GB', effective_io_concurrency = '200', random_page_cost = '1.1', search_path = 'public'
Planning:
  Buffers: shared hit=33 read=1 dirtied=2
Planning Time: 0.777 ms
Execution Time: 489.573 ms

Because “ProjectCode is a commonly used filter but also will be filtering by “name”, “ID”, or other columns in my ids table…” you need to have several indexes like:

CREATE INDEX _X_001a ON public.points ("projectCode", "includePoint", "dateTime", "ID") INCLUDE ("geom");
CREATE INDEX _X_001b ON public.points ("name", "includePoint", "dateTime", "ID") INCLUDE ("geom");
CREATE INDEX _X_001c ON public.points ("ID", "includePoint", "dateTime", ) INCLUDE ("geom");

…

And also this index

CREATE INDEX _X_002 ON public.ids ("ID", "projectCode") INCLUDE ("notes", "name");

Il they does not exists…

WHY ?

Setting “simple” indexes is not always enough for it to be taken into account by the optimizer to solve the optimal access to data

A simple index consists of an index key and that’s it. This index key can be made up of one or more columns of the table.

On the other hand, there are special clauses for creating indexes. Among these clauses, filtering (WHERE clause of the index), adding covering columns (INCLUDE clause), adding calculated expressions to the key, and, if the targeted RDBMS supports it, compression… All these things can significantly reduce the volume of pages to be acessed to obtain dataand therefore, be preferably chosen by the optimizer rather than scanning all the rows of the table.

It is the cardinality estimator which is based on data distribution statistics which calculates the approximate number of rows by correlating different hypotheses.
The cardinality estimator can be found wanting depending on the complexity of the search predicates.
For example, for a predicate that associates a column with a value by equality (COL = ‘val’) the complexity is low, and the estimate should be good.
On the other hand, for a predicate like LIKE with different wildcards (col LIKE ‘%val%’) the complexity is high, and the estimate will most likely be quite bad, unless the RDBMS provides special statistics to estimate in such a case (for example in MS SQL Server).

In addition, the margin of error of the estimate increases depending on the number of columns in the index key. So, if the margin of error is 5% on average in the case of a column, it can be 20% with 3 or 4 columns…
Then it is enough for this error to estimate too many pages to read for the optimizer to decide to scan the table instead of using the index in “search” mode…

One way to get around this is to put all the columns used in the query in the index. The ones searched in the key, the others in the INCLUDE.
this is to avoid a “seek” into the index values followed by a join with the table.

But the order of the columns in the index key is also very important, especially in BTree type indexes. You have to place the columns searchable by equality with a high dispersion first, then the ones searchable by inequality next, then the unsearched ones (NOT, <>…) at the end. And finally all other columns of the table in the INCLUDE clause to avoid the join from the index to the table…

Another way when the complexity of the searched value is very high (many rows with different predicates) is to use columnar indexes that free PostGreSQL does not support for OLTP databases…

Filed under: Kiến thức lập trình - @ 13:26

Thẻ: sqlpostgresqlquery-optimization

Thiết kế website giá rẻ

Danh mục

Query optimization on large PostgreSQL tables