MIME-Version: 1.0
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>
Date: Thu, 12 May 2011 14:07:03 -0700
Message-ID: <BANLkTikcLh2uKF085oFs7_zCSbcckrJB-Q@mail.gmail.com>
Subject: Re: Poor performance when joining against inherited tables
From: Maciek Sakrejda <msakrejda@truviso.com>
To: Lucas Madar <madar@samsix.com>
Cc: Robert Haas <robertmhaas@gmail.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

> 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