From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas F.O'Connell <tfo@sitening.com>
Cc: PgSQL - Performance <pgsql-performance@postgresql.org>
Subject: Re: Performance Anomalies in 7.4.5
Date: Thu, 21 Oct 2004 17:53:19 -0400
Message-ID: <25228.1098395599@sss.pgh.pa.us> (raw)
In-Reply-To: <D307B888-23A0-11D9-8068-000D93AE0944@sitening.com>
References: <D307B888-23A0-11D9-8068-000D93AE0944@sitening.com>
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
> Total runtime: 298321.926 ms
> (7 rows)
What's killing you here is that the planner thinks these tables are
completely empty (notice the zero cost estimates, which implies the
table has zero blocks --- the fact that the rows estimate is 1 and not 0
is the result of sanity-check clamping inside costsize.c). This leads
it to choose a nestloop, which would be the best plan if there were only
a few rows involved, but it degenerates rapidly when there are not.
It's easy to fall into this trap when truncating and reloading tables;
all you need is an "analyze" while the table is empty. The rule of
thumb is to analyze just after you reload the table, not just before.
I'm getting more and more convinced that we need to drop the reltuples
and relpages entries in pg_class, in favor of checking the physical
table size whenever we make a plan. We could derive the tuple count
estimate by having ANALYZE store a tuples-per-page estimate in pg_class
and then multiply by the current table size; tuples-per-page should be
a much more stable figure than total tuple count.
One drawback to this is that it would require an additional lseek per
table while planning, but that doesn't seem like a huge penalty.
Probably the most severe objection to doing things this way is that the
selected plan could change unexpectedly as a result of the physical
table size changing. Right now the DBA can keep tight rein on actions
that might affect plan selection (ie, VACUUM and ANALYZE), but that
would go by the board with this. OTOH, we seem to be moving towards
autovacuum, which also takes away any guarantees in this department.
In any case this is speculation for 8.1; I think it's too late for 8.0.
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, tfo@sitening.com
Subject: Re: Performance Anomalies in 7.4.5
In-Reply-To: <25228.1098395599@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