Обсуждение: Timestamp indexes aren't used for ">="
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
> (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
"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
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
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?