Re: difficulties with time based queries

От: Rainer Mager
Тема: Re: difficulties with time based queries
Дата: ,
Msg-id: 000501c9b89c$4cb4b510$e61e1f30$@com
(см: обсуждение, исходный текст)
Ответ на: difficulties with time based queries  ("Rainer Mager")
Ответы: Re: difficulties with time based queries  (PFC)
Список: pgsql-performance

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

difficulties with time based queries  ("Rainer Mager", )
 Re: difficulties with time based queries  (David Wilson, )
  Re: difficulties with time based queries  ("Rainer Mager", )
   Re: difficulties with time based queries  (Tom Lane, )
    Re: difficulties with time based queries  ("Rainer Mager", )
     Re: difficulties with time based queries  (Robert Haas, )
   Re: difficulties with time based queries  (Matthew Wakeling, )
 Re: difficulties with time based queries  (PFC, )
 Re: difficulties with time based queries  (Tom Lane, )
 Re: difficulties with time based queries  ("Rainer Mager", )
  Re: difficulties with time based queries  (PFC, )
   Re: difficulties with time based queries  (Nikolas Everett, )
 Re: difficulties with time based queries  ("Rainer Mager", )
  Re: difficulties with time based queries  (Tom Lane, )

So, I defragged my disk and reran my original query and it got a little better, but still far higher than I'd like. I then rebuilt (dropped and recreated) the ad_log_date_all index and reran the query and it is quite a bit better:

 

# explain analyze select * from ad_log where date(start_time) < date('2009-03-31') and date(start_time) >= date('2009-03-30');

                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on ad_log  (cost=64770.21..3745596.62 rows=2519276 width=32) (actual time=1166.479..13862.107 rows=2275167 loops=1)

   Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date))

   ->  Bitmap Index Scan on ad_log_date_all  (cost=0.00..64140.39 rows=2519276 width=0) (actual time=1143.582..1143.582 rows=2275167 loops=1)

         Index Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date))

 Total runtime: 14547.885 ms

 

 

During the query the disk throughput peaked at 30MB/s and was mostly at around 20MB/s, much better.

 

So, a few questions:

 

What can I do to prevent the index from getting bloated, or in whatever state it was in?

 

What else can I do to further improve queries on this table? Someone suggested posting details of my conf file. Which settings are most likely to be useful for this?

 

Any other suggestions?

 

 

Thanks,

 

--Rainer


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

От: "Rainer Mager"
Дата:
Сообщение: Re: difficulties with time based queries
От: Віталій Тимчишин
Дата:
Сообщение: Re: Nested query performance issue