Re: difficulties with time based queries

От: Rainer Mager
Тема: Re: difficulties with time based queries
Дата: ,
Msg-id: 003601c9b668$bf63c2d0$3e2b4870$@com
(см: обсуждение, исходный текст)
Ответ на: Re: difficulties with time based queries  (Tom Lane)
Ответы: Re: difficulties with time based queries  (Robert Haas)
Список: 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, )


> -----Original Message-----
> From: Tom Lane [mailto:]
> "Rainer Mager" <> writes:
> >> From: Tom Lane [mailto:]
> >> 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().)
>
> > Can you give me some more info on how to look at these stats?
>
> Since you've got 8.3 it's easy: select pg_relation_size('tablename')
> (or indexname).  The result is in bytes, so you might want to
> divide by 1K or 1M to keep the number readable.

Ok, nice and simple...I like it:

The result for the table ad_log, is 30,063 MB. The result for the index,
ad_log_date_all, is 17,151 MB. I guess this roughly makes sense since the
index is on 4 fields and the table only has 6 fields.

For the particular query I'm trying to optimize at the moment I believe I
should be able to use an index that references only 2 fields, which, I
imagine, should reduce the time needed to read it. I'll play with this a bit
and see what happens.

Any other suggestions?

--Rainer





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

От: Heikki Linnakangas
Дата:
Сообщение: Re: Best replication solution?
От: Robert Haas
Дата:
Сообщение: Re: difficulties with time based queries