Re: Re[2]: Weird indices

Jean-Christophe Boggio <cat(at)thefreecat(dot)org> writes:
> JS> I mean the explain shows that getting the count(*) from the field that
> JS> is indexed has to do a seq scan, presumably to determine if the rows are
> JS> in fact valid.

> count(*) means you want all the rows that have all the fields "not
> null". Read carefully : ALL THE FIELDS.

No, actually it just means "count the rows". count(f) for a field f
(or more generally any expression f) counts the number of non-null
values of f, but "*" just indicates count the rows.

Nonetheless, it's not that easy to keep a running count(*) total for a
table, even if we thought that select count(*) with no WHERE clause was
a sufficiently critical operation to justify slowing down every other
operation to keep the count(*) stats up to date. Think about committed
vs not-committed transactions. In the worst case, each active
transaction could have a different view of the table and thus a
different idea of what count(*) should yield; and each transaction might
have different pending updates that should affect the count(*) total
when and if it commits.

> ahem. One solution to the problem is known as "optimizer hints" in
> Oracle : you specify directly in the query HOW the optimizer should
> execute the query. It's very useful in various situations. I have
> asked Tom many times if that exists in PostgreSQL but didn't get any
> answer. I guess it's on a TODO list somewhere ;-)

Not on mine ;-). I don't believe in the idea, first because it's not
standard SQL, and second because I don't trust the user to know better
than the system what the best plan is in a particular context. Hints
that you put in last year may have been the right thing at the time
(or not...) but they'll still be lying there forgotten in your code
when the table contents and the Postgres implementation have changed
beyond recognition. Yes, the optimizer needs work, and it'll get that
work over time --- but a hint that's wrong is worse than no hint.
I'd rather have Postgres blamed for performance problems of its own
making than those of the user's making.

regards, tom lane