Re: difficulties with time based queries

От: Tom Lane
Тема: Re: difficulties with time based queries
Дата: ,
Msg-id: 5053.1238976759@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: difficulties with time based queries  ("Rainer Mager")
Список: 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, )

"Rainer Mager" <> writes:
> explain analyze select * from ad_log where date(start_time) <
> date('2009-03-31') and date(start_time) >= date('2009-03-30');

> Bitmap Heap Scan on ad_log  (cost=73372.57..3699152.24 rows=2488252
> width=32) (actual time=49792.862..64611.255 rows=2268490 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..72750.51
> rows=2488252 width=0) (actual time=49776.332..49776.332 rows=2268490
> loops=1)
>          Index Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
>  Total runtime: 65279.352 ms

Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
scan take the bulk of the runtime.  Usually that part is fast and where
the pain comes is in fetching from the heap.   I wonder whether that
index has become bloated.  How big are the table and the index
physically?  (Look at pg_class.relpages, or if you want a really
accurate number try pg_relation_size().)

What Postgres version is this, exactly?

BTW, I think you've gone way overboard in your indexing of this table;
those indexes are certainly consuming well more space than the table
does, and a lot of them are redundant.

            regards, tom lane


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

От: "Rainer Mager"
Дата:
Сообщение: Re: difficulties with time based queries
От: roopasatish
Дата:
Сообщение: probelm with alter table add constraint......