Optimizer confusion? - Mailing list pgsql-general

I have a table for which the SEQSCAN and INDEXSCAN estimates are the same
up to a point, after which the SEQSCAN estimates remain fixed, and the
indexscan estimates continue to grow. However, the actual speed of the
index scan is superior for a much greater period than the optimizer predicts.

The database has a table 'ping' with various fields including a 'pingtime
timestamp'; it also has a btree indexe on the date and has been 'vacuum
analyze'-ed. There are about 200000 rows and the data is evenly distributed
in 5 minute intervals.

These are the results from 'explain':

------ 1 ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'1-aug-1999';
NOTICE:  QUERY PLAN:

Index Scan using ping_ix1 on ping  (cost=0.00..4.28 rows=1 width=52)
------

This seems fine, even if the query is bogus.


------ 2 ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'2-aug-1999';
NOTICE:  QUERY PLAN:

Index Scan using ping_ix1 on ping  (cost=0.00..1679.29 rows=561 width=52)
------

Also looks OK.


------ 3 ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'3-aug-1999';
NOTICE:  QUERY PLAN:

Index Scan using ping_ix1 on ping  (cost=0.00..3091.18 rows=1123 width=52)
------

This seems OK; the estimate is roughly double the previous, which is to be
expected, I think.


------- 5 ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'5-aug-1999';
NOTICE:  QUERY PLAN:

Index Scan using ping_ix1 on ping  (cost=0.00..5386.70 rows=2245 width=52)
------

Again. this is OK, although I am a little surprised at the continuing
non-linearity of the estimates.

Now it starts getting very strange:

------- 5+a bit ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'5-aug-1999 20:25';
NOTICE:  QUERY PLAN:

Seq Scan on ping  (cost=0.00..6208.68 rows=2723 width=52)
-------

OK so far, but look at the following (the costs are the same):

------- 3 Months ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'1-nov-1999';
NOTICE:  QUERY PLAN:

Seq Scan on ping  (cost=0.00..6208.68 rows=51623 width=52)
-------

and

------- 5 + a YEAR ----
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'5-aug-2000 20:25';
NOTICE:  QUERY PLAN:

Seq Scan on ping  (cost=0.00..6208.68 rows=208184 width=52)
------


Now what is also strange, is if I set ENABLE_SEQSCAN=OFF, then the
estimates up to '5+a bit' are the *same*, but the running time is
substantially better for index scan. In fact the running time is better for
index scans up to an interval of about three months. I presume there is
something wrong with the selectivify estimates for the index.

I really don't want to have the code call 'SET ENABLE_SEQSCAN=OFF/ON'
around this statement, since for a longer period, I do want a sequential
scan. And building my own 'query optimizer' which says 'if time diff > 3
months, then enable seqscan' seems like a very bad idea.

I would be interested to know (a) if there is any way I can influence the
optimizer choice when it considers using the index in question, and (b) if
the fixed seqscan cost estimate is a bug.


FWIW, the output of a 3 month period with ENABLE_SEQSCAN=OFF is:

-----
uptime=# set enable_seqscan=off;
uptime=# explain select * from ping where pingtime>'1-aug-1999' and
pingtime<'1-nov-1999';
NOTICE:  QUERY PLAN:

Index Scan using ping_ix1 on ping  (cost=0.00..27661.01 rows=51623 width=52)
-----

Any help, explanation, etc would be appreciated.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/