Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
EXPLAIN says that the correct index is being used -- it didn't used
to. However, pg_stat* says otherwise. In my test, I have exactly one
dh value. Running EXPLAIN with this value produces a plan using idx_dh
(the correct index), but pg_s
Jack Orenstein <[EMAIL PROTECTED]> writes:
> EXPLAIN says that the correct index is being used -- it didn't used
> to. However, pg_stat* says otherwise. In my test, I have exactly one
> dh value. Running EXPLAIN with this value produces a plan using idx_dh
> (the correct index), but pg_stats says t
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
- I created two schemas, NOVAC and VAC, each with a table T as described above.
- Before loading data, I ran VACUUM ANALYZE on VAC.T.
- I then started loading data. The workload is a mixture of INSERT, SELECT and
UPDATE. For SELE
Jack Orenstein <[EMAIL PROTECTED]> writes:
> - I created two schemas, NOVAC and VAC, each with a table T as described
> above.
> - Before loading data, I ran VACUUM ANALYZE on VAC.T.
> - I then started loading data. The workload is a mixture of INSERT, SELECT
> and
> UPDATE. For SELECT and U
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
I may have simplified too far. Our application runs a number of
different queries. All our WHERE clauses restrict dh and fh. For a
given pair of (dh, fh) values, the initial query should come up empty
and then insert this pair, and then
Jack Orenstein <[EMAIL PROTECTED]> writes:
> I may have simplified too far. Our application runs a number of
> different queries. All our WHERE clauses restrict dh and fh. For a
> given pair of (dh, fh) values, the initial query should come up empty
> and then insert this pair, and then there is fu
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
If you plug in a value that *does* occur in the table it should probably
choose the more-relevant index consistently.
Unfortunately, it matters a lot at runtime. The dh value is not very selective,
as shown by the sta
Researching this some more, it appears to be the case that VACUUM (by itself, no
ANALYZE) is changing the optimizer's behavior. Here is a self-contained test:
select '*** drop t';
drop table t cascade;
select '*** create t(dh, fh, nm, filler)';
create table t (dh int, fh int, nm int, filler ch
Jack Orenstein <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> If you plug in a value that *does* occur in the table it should probably
>> choose the more-relevant index consistently.
> Unfortunately, it matters a lot at runtime. The dh value is not very
> selective,
> as shown by the statistic
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
If I run EXPLAIN on this query, (plugging in values 1 and 2 for the variables),
before VACUUM ANALYZE, I get the desired execution plan:
Index Scan using idx_df on T (cost=0.00..4.83 rows=1 width=454)
Index Cond: ((dh = 1) AND
Jack Orenstein <[EMAIL PROTECTED]> writes:
> If I run EXPLAIN on this query, (plugging in values 1 and 2 for the
> variables),
> before VACUUM ANALYZE, I get the desired execution plan:
> Index Scan using idx_df on T (cost=0.00..4.83 rows=1 width=454)
> Index Cond: ((dh = 1) AND (fh = 2))
I'm using postgres 7.4 and having a problem with the query optimizer. Our table,
T, looks like this:
dh int
fh int
nm int
... -- other columns
A typical row is 400-500 bytes.
T has two indexes, idx_df on (dh, fh) and idx_dn on (dh, nm).
My query is
12 matches
Mail list logo
