![]() ![]() 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. If it qualifies, keep fetching rows until the first one doesn't (super fast).You want start_date_time = something: index has the latest timestamp first.Should be the fastest possible way with a B-Tree index 1: These are queried with range conditions of inverted sort order ( =). Postgres can jump to id_phi = 0 in next to no time and consider the following two columns of the matching index. Since it's checked for equality ( =), it should come first. Sort order of the first column id_phi in the index is irrelevant. PostgreSQL index not used for query on range.But for range queries on multiple columns it can make a huge difference. Postgres can scan backwards practically as fast. In most cases the sort order of an index is hardly relevant. ON time_limits (id_phi, start_date_time, end_date_time DESC) Is it a viable option for my use case?įor Postgres 9.1 or later: CREATE INDEX idx_time_limits_ts_inverse I gather they can be more efficient on certain conditions for custom types. I have read a little about GIST and GIN indexes. Should I change from timestamp types to something else? Aren't they indexed by the primary key and idx_inversed I added? And I don't understand the big scan (60K rows!) on the timestamps. What could I do to optimize the search? You can see all the time is spent scanning the two timestamps columns once id_phi is set to 0. Index Cond: ((id_phi = 0) AND (start_date_time = ' 00:05:00'::timestamp without time zone)) Recheck Cond: ((id_phi = 0) AND (start_date_time = ' 00:05:00'::timestamp without time zone)) I tried explain analyze for the first time to get this query plan: Bitmap Heap Scan on time_limits (cost=475.32 rows=62682 width=36) (actual time=44.446.44.446 rows=0 loops=1) And twice more when targeting the end of the table (chronologically speaking). I got the impression that performance improved: The time for accessing records in the middle of the table seems to be more reasonable: somewhere between 40 and 90 seconds.īut it's still several tens of seconds for values in the middle of the time range. So I tried adding another index - the inverse of the PK: create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time) Queries like the following took enormous amounts of time: select * from time_limits as tĪnd t.start_date_time = timestamp' 00:05:00' Primary key(start_date_time, end_date_time,id_phi) There are additional columns in my actual table that are not involved with this query. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |