postgresqlspatialgist-index

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.)

Solution

  • 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.