Multiple Order By Criteria

От: J@Planeti.Biz
Тема: Multiple Order By Criteria
Дата: ,
Msg-id: 05c301c61bb1$d88c6a00$681e140a@fatchubby
(см: обсуждение, исходный текст)
Ответы: Re: Multiple Order By Criteria  (Josh Berkus)
Список: pgsql-performance

Скрыть дерево обсуждения

Multiple Order By Criteria  (, )
 Re: Multiple Order By Criteria  (Josh Berkus, )
  Re: Multiple Order By Criteria  (Stephan Szabo, )
 Re: Multiple Order By Criteria  (, )
  Re: Multiple Order By Criteria  (Fredrick O Jackson, )
 Re: Multiple Order By Criteria  (, )
  Re: Multiple Order By Criteria  (Stephan Szabo, )
 Re: Multiple Order By Criteria  (, )
  Re: Multiple Order By Criteria  ("Ahmad Fajar", )
   Re: Multiple Order By Criteria  (, )
    Re: Multiple Order By Criteria  (Stephan Szabo, )
   Re: Multiple Order By Criteria  (, )

I'm trying to query a table with 250,000+ rows. My query requires I provide 5 colums in my "order by" clause:
 
select
   column
from table
where
 column >= '2004-3-22 0:0:0'
order by
    ds.receipt desc,
    ds.carrier_id asc,
    ds.batchnum asc,
    encounternum asc,
    ds.encounter_id ASC
limit 100 offset 0
 
I have an index built for each of these columns in my order by clause. This query takes an unacceptable amount of time to execute. Here are the results of the explain:
 
Limit  (cost=229610.78..229611.03 rows=100 width=717)
  ->  Sort  (cost=229610.78..230132.37 rows=208636 width=717)
        Sort Key: receipt, carrier_id, batchnum, encounternum, encounter_id
        ->  Seq Scan on detail_summary ds  (cost=0.00..22647.13 rows=208636 width=717)
              Filter: (receipt >= '2004-03-22'::date)
 
When I have the order by just have 1 criteria, it's fine (just ds.receipt DESC)
 
Limit  (cost=0.00..177.71 rows=100 width=717)
  ->  Index Scan Backward using detail_summary_receipt_id_idx on detail_summary ds  (cost=0.00..370756.84 rows=208636 width=717)
        Index Cond: (receipt >= '2004-03-22'::date)
 
I've increased my work_mem to up to 256meg with no speed increase. I think there's something here I just don't understand.
 
How do I make this go fast ?

 
 
 
 

В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Suspending SELECTs
От: Tom Lane
Дата:
Сообщение: Re: Getting pg to use index on an inherited table (8.1.1)