Обсуждение: Timestamp indexes aren't used for ">="

Поиск
Список
Период
Сортировка

Timestamp indexes aren't used for ">="

От
"Jeff Boes"
Дата:
We have a table which has approximately 400,000 rows.
It has 17 columns, and 4 indexes.  The primary key
is a int4 (filled by a sequence), additionally we
have two more int4 indexes and a timestamp index.

The documentation for create index
(http://www.postgresql.org/idocs/index.php?sql-createindex.html)
gives a nice query to display the list of known operators
for each of the index types.  Running this yields the
following useful data....

  acc_name |   ops_name    | ops_comp
----------+---------------+----------
  btree    | timestamp_ops | <
  btree    | timestamp_ops | <=
  btree    | timestamp_ops | =
  btree    | timestamp_ops | =
  btree    | timestamp_ops | >
  btree    | timestamp_ops | >=

Now, if this is true.... how can this result be consistant?

(selecting by equality with timestamp value)

explain select fetch_status, count(*)
from stat_fetch where fetch_date = '2001-11-08 00:00:00-05'
group by fetch_status;

NOTICE:  QUERY PLAN:

Aggregate  (cost=8.05..8.06 rows=1 width=12)
   ->  Group  (cost=8.05..8.05 rows=1 width=12)
         ->  Sort  (cost=8.05..8.05 rows=1 width=12)
               ->  Index Scan using ix_stat_fetch_3 on stat_fetch
(cost=0.00..8.04 rows=1 width=12)

EXPLAIN

.....BUT.....

(selecting by comparison ">=" to timestamp value)

explain select fetch_status, count(*)
from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
group by fetch_status;

NOTICE:  QUERY PLAN:

Aggregate  (cost=12322.64..12522.06 rows=3988 width=12)
   ->  Group  (cost=12322.64..12422.35 rows=39884 width=12)
         ->  Sort  (cost=12322.64..12322.64 rows=39884 width=12)
               ->  Seq Scan on stat_fetch  (cost=0.00..8917.33
rows=39884 width=12)

EXPLAIN

.....AND YET.....

set enable_seqscan to off;
explain select fetch_status, count(*)
from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
group by fetch_status;
NOTICE:  QUERY PLAN:

Aggregate  (cost=38193.97..38393.39 rows=3988 width=12)
   ->  Group  (cost=38193.97..38293.68 rows=39884 width=12)
         ->  Sort  (cost=38193.97..38193.97 rows=39884 width=12)
               ->  Index Scan using ix_stat_fetch_3 on stat_fetch
(cost=0.00..34788.66 rows=39884 width=12)

EXPLAIN

Note the cost of the "Index" scan is actually a higher estimate
than the sequential scan.

This leads me to the conclusion that either postgres has a
bug that is preventing it from actually using the operator
that is defined on the index (thus falling back to the non-
indexed comparison), or explain is broken, or my understanding
of indexes is broken.


--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

Re: Timestamp indexes aren't used for ">="

От
Stephan Szabo
Дата:
> (selecting by equality with timestamp value)
>
> explain select fetch_status, count(*)
> from stat_fetch where fetch_date = '2001-11-08 00:00:00-05'
> group by fetch_status;
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=8.05..8.06 rows=1 width=12)
>    ->  Group  (cost=8.05..8.05 rows=1 width=12)
>          ->  Sort  (cost=8.05..8.05 rows=1 width=12)
>                ->  Index Scan using ix_stat_fetch_3 on stat_fetch
> (cost=0.00..8.04 rows=1 width=12)
>
> EXPLAIN
>
> .....BUT.....
>
> (selecting by comparison ">=" to timestamp value)
>
> explain select fetch_status, count(*)
> from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
> group by fetch_status;
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=12322.64..12522.06 rows=3988 width=12)
>    ->  Group  (cost=12322.64..12422.35 rows=39884 width=12)
>          ->  Sort  (cost=12322.64..12322.64 rows=39884 width=12)
>                ->  Seq Scan on stat_fetch  (cost=0.00..8917.33
> rows=39884 width=12)
>
> EXPLAIN
>
> .....AND YET.....
>
> set enable_seqscan to off;
> explain select fetch_status, count(*)
> from stat_fetch where fetch_date >= '2001-11-08 00:00:00-05'
> group by fetch_status;
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=38193.97..38393.39 rows=3988 width=12)
>    ->  Group  (cost=38193.97..38293.68 rows=39884 width=12)
>          ->  Sort  (cost=38193.97..38193.97 rows=39884 width=12)
>                ->  Index Scan using ix_stat_fetch_3 on stat_fetch
> (cost=0.00..34788.66 rows=39884 width=12)
>
> EXPLAIN
>
> Note the cost of the "Index" scan is actually a higher estimate
> than the sequential scan.

How many rows are in the table?  Have you run vacuum analyze?

It's estimating that about 40000 of them will match the condition, is
this a reasonable estimate?  If so, you're reading about 1/10
of the rows (assuming a small number of dead rows). Because the
tuple validity information is stored in the heap file, you need
to load the heap pages for those rows that match the index condition.

I think it tries to estimate the cost of:
 reading the index + reading the heap file for the matching rows
  (including the seeking necessary to move around to the correct
   page)
vs the cost of:
 reading the heap file sequentially



Re: Timestamp indexes aren't used for ">="

От
Tom Lane
Дата:
"Jeff Boes" <jboes@nexcerpt.com> writes:
> This leads me to the conclusion that either postgres has a
> bug that is preventing it from actually using the operator
> that is defined on the index (thus falling back to the non-
> indexed comparison), or explain is broken, or my understanding
> of indexes is broken.

The latter.  You are neglecting the fact that an indexscan on an
'=' condition (scan only those values '=' to something) is normally
a lot more selective than an indexscan on a '>=' condition (scan
from that value to the end).

In your example, the '=' condition is estimated to select only
one row, whereas the '>=' condition is estimated to select
39884 rows, or about 10% of the table.  If that estimate is
accurate then very probably the planner made the right choice
--- indexscans that touch more than a couple percent of the table
are normally losers in Postgres, compared to a simple sequential
scan (mainly because Unix kernels like to read ahead on sequential
reads, so seqscan cooperates with the kernel instead of fighting it).

Since you didn't tell us how many rows are really involved, nor
what the actual runtimes were with and without the enable_seqscan
change, we can't tell how close the planner's estimates are to
reality.  But there's no a-priori evidence of brokenness here.

            regards, tom lane

Re: Timestamp indexes aren't used for ">="

От
"Jeff Boes"
Дата:
In article <20011109145054.H59285-100000@megazone23.bigpanda.com>,
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:

> How many rows are in the table?  Have you run vacuum analyze?

Sorry, that information was in the original post, but perhaps you missed
it:

In article <9shhnf$23ks$1@news.tht.net>, "Jeff Boes" <jboes@nexcerpt.com>
wrote:

> We have a table which has approximately 400,000 rows. It has 17 columns,
> and 4 indexes.  The primary key is a int4 (filled by a sequence),
> additionally we have two more int4 indexes and a timestamp index.

Yes, VACUUM ANALYZE gets run every 24 hours, and currently the table
grows by some 25K-40K rows per day.  Could a factor be the time elapsed
between the VACUUM and the query?



--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

Re: Timestamp indexes aren't used for ">="

От
Stephan Szabo
Дата:
On Mon, 12 Nov 2001, Jeff Boes wrote:

> In article <20011109145054.H59285-100000@megazone23.bigpanda.com>,
> "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:
>
> > How many rows are in the table?  Have you run vacuum analyze?
>
> Sorry, that information was in the original post, but perhaps you missed
> it:
>
> In article <9shhnf$23ks$1@news.tht.net>, "Jeff Boes" <jboes@nexcerpt.com>
> wrote:
>
> > We have a table which has approximately 400,000 rows. It has 17 columns,
> > and 4 indexes.  The primary key is a int4 (filled by a sequence),
> > additionally we have two more int4 indexes and a timestamp index.
>
> Yes, VACUUM ANALYZE gets run every 24 hours, and currently the table
> grows by some 25K-40K rows per day.  Could a factor be the time elapsed
> between the VACUUM and the query?

Is the 40000 row estimate for the number selected correct?  If so, then
index scan may very well be a losing plan for this query.  Does the forced
index scan actually take less time than the the sequence scan?