Question 276080 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to make a composite index in Postgres including a POINT?

With this table:

CREATE TABLE bins (
    id SERIAL PRIMARY KEY,
    start TIMESTAMP NOT NULL,
    bits BIT(16) NOT NULL,
    topleft POINT, -- (x,y) in web mercator projection
    count INTEGER
);
CREATE INDEX ON bins USING gist(topleft);

How can I create a composite index such that I can efficiently run queries such as:

SELECT SUM(packets) FROM bins
WHERE (start BETWEEN '2023-10-30' AND '2023-10-31')
AND bits = B'0000000000001001'
AND topleft <@ BOX '(90500000000,135800000000)(90600000000,135900000000)';

(Composite indices, not involving gists, are not a problem.)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found I needed to use CREATE EXTENSION btree_gist; which then allowed me to use:

    CREATE INDEX ON bins USING gist(start, bits, topleft);

    I'm not sure if this is the best solution.


  2. 0

    The index in your answer looks alright. To avoid scanning unnecessary index entries, you should put the columns where you compare with = first. In your case, that is bits.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search