Custom operator class costs
От | Ben Beecher |
---|---|
Тема | Custom operator class costs |
Дата | |
Msg-id | AANLkTikjpZojDysWA5au+2pYPJeag4toJjh62YncwMog@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Custom operator class costs
|
Список | pgsql-performance |
Hey! I'm having some trouble optimizing a query that uses a custom operator class. #Postgres has given me a solution for natural sort - http://www.rhodiumtoad.org.uk/junk/naturalsort.sql I'm trying to run it over a huge table - when running it on demand, the data needs to be dumped to memory and sorted. Sort (cost=31299.83..31668.83 rows=369 width=31) Sort Key: name -> Seq Scan on solutions_textbookpage (cost=0.00..25006.55 rows=369 width=31) Filter: (active AND (textbook_id = 263)) That's obviously too slow. I've created an index using the custom operator class, so I don't have to do the sort every time I try to sort. Index Scan Backward using natural_page_name_textbook on solutions_textbookpage (cost=0.00..650.56 rows=371 width=31) (actual time=0.061..0.962 rows=369 loops=1) Index Cond: (textbook_id = 263) Filter: active Obviously a little faster! The problem I'm having is that because operator classes have a low cost estimation pg missestimates and tries to do the sort on demand rather than using the index. I can get pg to use the index by either jacking up cpu_operator_cost or lowering random_page_cost. Is this the best way to do that, or is there a smarter way to ensure that pg uses this index when I need it.
В списке pgsql-performance по дате отправления: