From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark kirkwood <markir@slingshot.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Different Choices For Index/Sequential Scan With And Without A Join In 7.2
Date: Sun, 26 Aug 2001 11:44:54 -0400
Message-ID: <18604.998840694@sss.pgh.pa.us> (raw)
In-Reply-To: <01082620361000.01707@spikey.slithery.org>
References: <01082620361000.01707@spikey.slithery.org>
Mark kirkwood <markir@slingshot.co.nz> writes:
> Note that 'f1' = '2000-01-26' corrosponds to 'd0key' = 270 in the table
> 'dim0';
What do you mean by "corresponds to"? Is there a one-to-one mapping
between distinct values of fact0.d0key and distinct values of dim0.f1?
Or do you just mean that the values play corresponding roles in these
two queries?
> I used cpu_tuple_cost = 0.4, but everything else was fairly standard.
?? You're claiming that the CPU time involved in processing a single
tuple is 40% as large as the time to fetch a page from disk. Unless
you're running a high-end RAID array attached to an ENIAC, I don't
believe it. This adjustment almost certainly will produce silly results.
> It guess I was expecting the value that made the first query change
> from index to seq scan to be "close" to the value that made the second
> query use a sequential scan...
Um, are you considering the effects of statistical density of the
values? I see no particular reason to assume that a range of nine days
in a date column should be equally as selective as a range of nine
counts in an integer key column. It all depends on what fraction of the
table entries actually fall within those ranges.
Have you looked at the ANALYZE statistics for the tables? (You have
done an ANALYZE on them, I hope.) Try
select * from pg_stats where tablename = 'fact';
The user documentation about 7.2 statistics is nonexistent as yet, but
you can read src/include/catalog/pg_statistic.h for info.
If the tables are large and have irregular distributions, you might find
that increasing the statistics target value for the key columns helps
the optimizer to produce good plan choices. See ALTER TABLE SET
STATISTICS. I'd be interested to hear about it if so --- the current
default target of 10 was picked "out of the air" and might well be
off-base.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, markir@slingshot.co.nz
Subject: Re: Different Choices For Index/Sequential Scan With And Without A Join In 7.2
In-Reply-To: <18604.998840694@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