From: Maciek Sakrejda <msakrejda@truviso.com>
To: Lucas Madar <madar@samsix.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor performance when joining against inherited tables
Date: Thu, 12 May 2011 14:07:03 -0700
Message-ID: <BANLkTikcLh2uKF085oFs7_zCSbcckrJB-Q@mail.gmail.com> (raw)
In-Reply-To: <sig.81124526f3.4DCAF5F9.10904@samsix.com>
References: <4DA3608E.9090906@samsix.com>
<BANLkTimssX0BS_ycZjye3z7TLiiiPqi8tg@mail.gmail.com>
<sig.81124526f3.4DCAF5F9.10904@samsix.com>
> It says the sequential scan has a cost that's way too high, and I'm
> presuming that's why it's choosing the extremely slow plan over the much
> faster plan.
Well, not exactly. It's giving you that cost because you disabled
seqscan, which actually just bumps the cost really high:
postgres=# create temporary table foo as select generate_series(1,3);
SELECT
postgres=# explain analyze select * from foo;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..34.00 rows=2400 width=4) (actual
time=0.010..0.012 rows=3 loops=1)
Total runtime: 2.591 ms
(2 rows)
postgres=# set enable_seqscan to false;
SET
postgres=# explain analyze select * from foo;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=10000000000.00..10000000034.00 rows=2400
width=4) (actual time=0.004..0.007 rows=3 loops=1)
Total runtime: 0.037 ms
(2 rows)
As far as I know, there is no hard way to disable any given plan
option, since sometimes that may be the only choice.
The (estimated) cost of the seq scan chosen here is *not* the same as
the cost of the scan when the planner actually considers this plan (in
fact, that will the same as the one in the first plan).
However, note the cost of the Index Scan nodes in the second plan:
they are *higher* than their corresponding Seq Scan nodes (in the
first plan), which is why you get the first plan when seq can *is*
enabled.
Also, your plan output looks like plain EXPLAIN and not EXPLAIN
ANALYZE (i.e., the "actual time" nodes are missing).
Other than that, I think Shaun's comments apply.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
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: msakrejda@truviso.com, madar@samsix.com, robertmhaas@gmail.com
Subject: Re: Poor performance when joining against inherited tables
In-Reply-To: <BANLkTikcLh2uKF085oFs7_zCSbcckrJB-Q@mail.gmail.com>
* 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