Ah no, I think both of you have mistaken me. The problem here is not about partial indexes (not really anyway).
I do have a partial index with "WHERE purge_date IS NOT NULL", and my query does contain "WHERE purge_date IS NOT NULL" as well. The problem here is, that all rows (or almost all) have the column purge_date set to NULL. The planner expected the query to return 33% of all rows in the table. So it made the seq scan MUCH cheaper, which was right in the planner's way of thinking because it didn't know anything about the column from pg_stats.

I had a look at the source code of the analyze command meanwhile:
the compute_*_stats functions don't return valid statistics if they can't find any non-null values, and as a result no statistics tuple for that column is created in pg_stats. I think this is wrong. Not finding any non-null values IS a very useful information, it means a null-fraction of 100%. I have patched my postgres to return valid statistics even in that case (patch below).
The difference now is that the planner doesn't assume anymore it would get about 33% of rows back, instead it knows that the null-fraction of that column is approximately 1.0 and it chooses the index scan because that is now the by far cheapest plan.

--- analyze.c   Thu Jan 20 11:37:58 2005
+++ analyze.c.orig      Sun Nov 14 03:04:13 2004
@@ -1704,9 +1704,6 @@
                        stats->stavalues[0] = mcv_values;
                        stats->numvalues[0] = num_mcv;
-       } else {
-               stats->stats_valid = true;
-               stats->stanullfrac = 1.0;

/* We don't need to bother cleaning up any of our temporary palloc's */
@@ -2164,9 +2161,6 @@
stats->numnumbers[slot_idx] = 1;
- } else {
- stats->stats_valid = true;
- stats->stanullfrac = 1.0;

/* We don't need to bother cleaning up any of our temporary palloc's */

On 21.01.2005, at 7:02 Uhr, Tom Lane wrote:

Bruno Wolff III <[EMAIL PROTECTED]> writes:
Bernd Heller <[EMAIL PROTECTED]> wrote:
there is no entry in pg_stats for that column at all!! I can only
suspect that this has to do with the column being all null.

Someone else reported this recently and I think it is going to be fixed.

Yeah, this was griped of a little bit ago, but I felt it was too close to 8.0 release to risk fooling with for this cycle.

In the short run you could add an IS NOT NULL clause to your query.
The optimizer doesn't know that < being TRUE implies IS NOT NULL and
so the partial index won't be used unless you add that clause explicitly.

Actually, as of 8.0 the optimizer *does* know that. I'm a bit surprised
that it didn't pick the partial index, since even without any analyze
stats, the small physical size of the partial index should have clued it
that there weren't many such tuples. Could we see EXPLAIN output for
both cases (both settings of enable_seqscan)?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to