PostgreSQL using the wrong Index
От | Alex Stapleton |
---|---|
Тема | PostgreSQL using the wrong Index |
Дата | |
Msg-id | 2D1A68BF-B3A4-490E-8053-3CF6626B8DAF@advfn.com обсуждение исходный текст |
Ответы |
Re: PostgreSQL using the wrong Index
Re: PostgreSQL using the wrong Index |
Список | pgsql-performance |
We have two index's like so l1_historical=# \d "N_intra_time_idx" Index "N_intra_time_idx" Column | Type --------+----------------------------- time | timestamp without time zone btree l1_historical=# \d "N_intra_pkey" Index "N_intra_pkey" Column | Type --------+----------------------------- symbol | text time | timestamp without time zone unique btree (primary key) and on queries like this select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST' order by time desc limit 1; PostgreSQL takes a very long time to complete, as it effectively scans the entire table, backwards. And the table is huge, about 450 million rows. (btw, there are no triggers or any other exciting things like that on our tables in this db.) but on things where the symbol does exist in the table, it's more or less fine, and nice and fast. Whilst the option the planner has taken might be faster most of the time, the worst case scenario is unacceptable for obvious reasons. I've googled for trying to force the use of a specific index, but can't find anything relevant. Does anyone have any suggestions on getting it to use an index which hopefully will have better worst case performance?
В списке pgsql-performance по дате отправления: