pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard van den Berg <richard.vandenberg@trust-factory.com>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: When are index scans used over seq scans?
Date: Wed, 20 Apr 2005 10:39:21 -0400
Message-ID: <26710.1114007961@sss.pgh.pa.us> (raw)
In-Reply-To: <426665A0.1080102@trust-factory.com>
References: <426665A0.1080102@trust-factory.com>

Richard van den Berg <richard.vandenberg@trust-factory.com> 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



view thread (13+ messages)  latest in thread

Message-ID: <26710.1114007961@sss.pgh.pa.us>
Permalink:  ../26710.1114007961@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26710.1114007961@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, richard.vandenberg@trust-factory.com
  Subject: Re: When are index scans used over seq scans?
  In-Reply-To: <26710.1114007961@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox