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
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