Re: performance of IN (subquery)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I've thought about this before. One simple trick would be to get rid of the
> current pg_class reltuples/relpages fields in favor of a tuples-per-page
> estimate, which could be multiplied by RelationGetNumberOfBlocks() during
> planning.

This would do something interesting to one of the problem cases I have now. I
have trouble testing a particular batch job that generates a large amount of
precalculated denormalized data.

That's because the data is empty when the job starts, so the plpgsql function
that handles the job caches plans based on an empty table. But as the job
proceeds the data grows and I'm afraid the cached plan may start performing
poorly.

In order to test it I need to run it once, analyze, then reload the function,
truncate the data and run it another time. And hope I generated good
representative test data.

I'm thinking of changing to a non-plpgsql implementation. But that's only half
the issue. I'm not about to run analyze in the middle of the data generation
(which wouldn't work anyways since it's in a transaction). So I can't really
get good statistics for this job, not until we're actually in a steady state
in production.

Sometimes I wonder whether I wouldn't rather a more predictable system with
less focus on statistics. The resulting plans would be more predictable and
predictability is a good thing in production systems...

> In the absence of any ANALYZE data the tuples-per-page estimate might be
> pretty bogus, but it couldn't be off by more than an order of magnitude or
> so either way.

I don't see why it couldn't. If you have a table badly in need of vacuuming
(or had one at the time of the last analyze) it could be off by way more than
an order of magnitude.

For that matter, a table that had undergone many deletes and then been
vacuumed would not change length for a long time afterward even as many new
inserts are performed. Until the table is analyzed the estimated table size
could be off by an arbitrary factor.

> The objections that could be raised to this are (AFAICS) two:
>
> 1. Adding at least an lseek() kernel call per table, and per index, to
> every planning operation. I'm not sure this would be significant,
> but I'm not sure it wouldn't be, either.

That seems like something that could be addressed with enough time. A single
value per table, couldn't it be stored in shared memory and only updated
whenever the heap is extended or truncated? Even if you have thousands of
tables that would only be a few kilobytes of shared memory.

> 2. Instability of plans. Right now, the planner will not change plans
> underneath you --- you have to issue an explicit VACUUM or ANALYZE
> to change the terms of discussion. That would stop being true if
> physical file size were always taken into account. Maybe this is a
> problem, or maybe it isn't ... as someone who likes to be able to
> debug planner behavior without actually creating umpteen-gig test
> tables, my world view may be a bit skewed ...

This is what I'm afraid of. As a OLTP application programmer -- web sites, I
admit -- I care a lot more about plan stability than finding optimal plans.

An well written OLTP application will only have a fixed number of queries that
are executed repeatedly with different parameters. I don't care how long the
queries take as long as they're always "fast enough".

Every time a new plan is tried there's a chance that it will be wrong. It only
takes one wrong plan out of the hundreds of queries to bring down the entire
application.

Ideally I would want a guarantee that every query would *always* result in the
same plan. Once I've tested them and approved the plans I want to know that
only those approved plans will ever run, and I want to be present and be able
to verify new plans before they go into production.

I doubt I'm going to convince anyone today, but I think there will be a
gradual change in mindset as the new binary protocol becomes more popular. And
postgres takes over some of mysql's web mindshare.

--
greg