Re: When are index scans used over seq scans?

От: Tom Lane
Тема: Re: When are index scans used over seq scans?
Дата: ,
Msg-id: 26710.1114007961@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: When are index scans used over seq scans?  (Richard van den Berg)
Ответы: Re: When are index scans used over seq scans?  (Richard van den Berg)
Re: When are index scans used over seq scans?  (Richard van den Berg)
Список: pgsql-performance

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

When are index scans used over seq scans?  (Richard van den Berg, )
 Re: When are index scans used over seq scans?  (John A Meinel, )
  Re: When are index scans used over seq scans?  (Richard van den Berg, )
   Re: When are index scans used over seq scans?  (John A Meinel, )
    Re: When are index scans used over seq scans?  (Richard van den Berg, )
 Re: When are index scans used over seq scans?  (Tom Lane, )
  Re: When are index scans used over seq scans?  (Richard van den Berg, )
  Re: When are index scans used over seq scans?  (Richard van den Berg, )
   Re: When are index scans used over seq scans?  (Tom Lane, )
    Re: When are index scans used over seq scans?  (Richard van den Berg, )
     Re: When are index scans used over seq scans?  (Tom Lane, )
      Re: When are index scans used over seq scans?  (Richard van den Berg, )
 Re: When are index scans used over seq scans?  (Richard van den Berg, )

Richard van den Berg <> writes:
> We have a table with 1M rows that contain sessions with a start and
> finish timestamps. When joining this table with a 10k table with rounded
> timestamps, explain shows me sequential scans are used, and the join
> takes about 6 hours (2s per seq scan on session table * 10000):

>  Nested Loop  (cost=252.80..233025873.16 rows=1035480320 width=97)
> Join Filter: (("outer".starttime <= "inner".ts) AND ("outer".finishtime
>> = "inner".ts))
>    ->  Seq Scan on sessions us  (cost=0.00..42548.36 rows=924536
> width=105)    ->  Materialize  (cost=252.80..353.60 rows=10080 width=8)
>          ->  Seq Scan on duration du  (cost=0.00..252.80 rows=10080 width=8)

The explain shows no such thing.  What is the *actual* runtime of
each plan per EXPLAIN ANALYZE, please?

(In general, any time you are complaining about planner misbehavior,
it is utterly pointless to give only planner estimates and not reality.
By definition, you don't think the estimates are right.)

            regards, tom lane


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

От: "Anjan Dave"
Дата:
Сообщение: Re: Joel's Performance Issues WAS : Opteron vs Xeon
От: John A Meinel
Дата:
Сообщение: Re: Joel's Performance Issues WAS : Opteron vs Xeon