![]() But the query will probably still be faster overall with the combined index.Ĭhange your table definition or use an expression index.įor the multicolumn GiST index at hand you also need the additional module btree_gist installed (once per database) which provides the operator classes to include an integer. A btree index is typically faster for an additional integer column like id_phi. There are built-in range types for timestamp and timestamp with time zone: tsrange, tstzrange. GiST index in Postgres 9.2+ġ With pg 9.2+ there is another, possibly faster option: a GiST index for a range column. It's a one-time effect deteriorating over time with writes on the table fragmenting the physical sort order. Or consider pg_repack or the later pg_squeeze, which can do the same without exclusive lock on the table.Įither way, the effect is that fewer blocks need to be read from the table and everything is pre-sorted. If you can afford to lock your table exclusively (at off hours for instance), rewrite your table and order rows according to the index with CLUSTER: CLUSTER time_limits USING idx_time_limits_inversed If you want it faster, yet, you could streamline the physical order of rows in your table. I also don't think it matters much with the improved index. I guess you don't need that for id_phi (only few distinct values, evenly distributed), but for the timestamps (lots of distinct values, unevenly distributed). ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000 ĪLTER TABLE time_limits ALTER end_date_time SET STATISTICS 1000 You might get better estimates if you increase the statistics target for the table. The planner estimates rows=62682 for your example query. But almost all rows with id_phi = 0 at the chronological end of the table! So performance deteriorates with later start times. Only few with a start_date_time close to the start of the time range of the table. How many rows match on the first two columns? Be sure to read the chapter Indexes and ORDER BY in the manual. The way you had the index, it has to read all rows matching on the first two columns and then filter on the third. Postgres can either scan forward or backward.
0 Comments
Leave a Reply. |