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: Thu, 21 Apr 2005 10:25:02 -0400
Message-ID: <15074.1114093502@sss.pgh.pa.us> (raw)
In-Reply-To: <4267612C.10803@trust-factory.com>
References: <426665A0.1080102@trust-factory.com>
	<26710.1114007961@sss.pgh.pa.us>
	<4267612C.10803@trust-factory.com>

Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
> This is with the default cpu_tuple_cost = 0.01:

>  Nested Loop  (cost=252.80..233010147.16 rows=1035480320 width=98)
> (actual time=0.369..12672213.137 rows=6171334 loops=1)
>    Join Filter: (("outer".starttimetrunc <= "inner".ts) AND
> ("outer".finishtimetrunc >= "inner".ts))
>    ->  Seq Scan on sessions us  (cost=0.00..26822.36 rows=924536
> width=106) (actual time=0.039..5447.349 rows=924536 loops=1)
>    ->  Materialize  (cost=252.80..353.60 rows=10080 width=8) (actual
> time=0.000..2.770 rows=10080 loops=924536)
>          ->  Seq Scan on duration du  (cost=0.00..252.80 rows=10080
> width=8) (actual time=0.019..13.397 rows=10080 loops=1)
>  Total runtime: 12674486.670 ms

Hmm, that *is* showing rather a spectacularly large amount of time in
the join itself: if I did the arithmetic right,

regression=# select 12672213.137 - (5447.349 + 2.770*924536 + 13.397);
   ?column?
--------------
 10105787.671
(1 row)

which is almost 80% of the entire runtime.  Which is enormous.
What are those column datatypes exactly?  Perhaps you are incurring a
datatype conversion cost?  Straight timestamp-vs-timestamp comparison
is fairly cheap, but any sort of conversion will cost dearly.

The planner's model for the time spent in the join itself is
	(cpu_tuple_cost + 2 * cpu_operator_cost) * n_tuples
(the 2 because you have 2 operators in the join condition)
so you'd have to raise one or the other of these parameters
to model this situation accurately.  But I have a hard time
believing that cpu_tuple_cost is really as high as 0.1.
It seems more likely that the cpu_operator_cost is underestimated,
which leads me to question what exactly is happening in those
comparisons.

			regards, tom lane



view thread (13+ messages)  latest in thread

Message-ID: <15074.1114093502@sss.pgh.pa.us>
Permalink:  ../15074.1114093502@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15074.1114093502@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: <15074.1114093502@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