Hello,

After fresh install of PostgreSQL 7.1.3 I was having one particular query 
(JOIN), running for several hours. Upon closer investigation, it was returning 
weird EXPLAIN 'optimisations' (in essence, doing 'index' searches on fields 
that were not constrained in the query etc). The same query has reasonable 
EXPLAIN and executes fine under 7.0.2.

I tried to re-create the table by table, starting with the following:

CREATE TABLE r (
	a integer,
	b integer,
	c integer,
	d integer
);

CREATE INDEX r_d_idx on r(d);

COPY r FROM stdin;
1	4234	4324	4
1	4342	886	8
[...]
\.

(table has ~30k rows)

EXPLAIN SELECT * FROM r where d = 8;

The result is 

NOTICE: QUERY PLAN:

Seq Scan on r (cost=0.00...3041.13 rows=7191 width=4)

Does not matter if I VACUUM ANALYZE the table or the whole database.

Any ideas why this happens?

PostgreSQL is compiled with 

./configure --enable-locale --with-perl --with-python --with-tcl --enable-odbc 
--with-krb4 --with-openssl --enable-syslog --with-includes=/usr/include/kerbero
sIV:/usr/contrib/include

this, under BSD/OS 4.2

Thanks in advance for any ideas,
Daniel


Mon, 03 Dec 2001 18:59:45 +0200
Daniel Kalchev <daniel@digsys.bg>
7.1.3 not using index
Daniel Kalchev <daniel@digsys.bg> writes:
> (table has ~30k rows)
> EXPLAIN SELECT * FROM r where d = 8;
> The result is 
> NOTICE: QUERY PLAN:
> Seq Scan on r (cost=0.00...3041.13 rows=7191 width=4)

Seqscan is the right plan to retrieve 7k rows out of a 30k table.
So the question is whether that estimate is in the right ballpark
or not.  How many rows are there really with d=8?  If it's way off,
what do you get from

select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'r';

			regards, tom lane

Mon, 03 Dec 2001 12:34:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 
Tom,

You may be correct that sequential scan is preferable, but I can never get 
version 7.1.3 to use index scan on almost any table. Here is the output of 
your query:

     attname     | attdispersion | starelid | staattnum | staop | stanullfrac 
| stacommonfrac | stacommonval | staloval | stahival
-----------------+---------------+----------+-----------+-------+-------------+
---------------+--------------+----------+----------
 a               |      0.978655 |  8160023 |         1 |    97 |           0 
|      0.988079 | 1            | 1        | 52
 b               |   2.86564e-05 |  8160023 |         2 |    97 |           0 
|     0.0001432 | 4971         | 1        | 12857
 c               |   0.000520834 |  8160023 |         3 |    97 |           0 
|     0.0025776 | 1            | 1        | 11309
 d               |      0.104507 |  8160023 |         4 |    97 |           0 
|      0.257437 | 8            | 1        | 32

In fact, field 'd' has only few values - usually powers of 2 (history). Values 
are respectively 1,2,4,8. 16 and 32 and are spread like:


 person_type | count 
-------------+-------
           1 |  8572
           2 |  3464
           4 |  8607
           8 |  7191
          16 |     3
          32 |    96
(6 rows)

Some estimates are weird, such as:

db=# explain select * from r where d = 16;
NOTICE:  QUERY PLAN:

Seq Scan on r  (cost=0.00..527.16 rows=719 width=16)

I also tried the same query where the value exists only once in the table - 
one would expect this is the perfect use of index...

I also note very slow response to any queries that access systems tables, such 
as \d in psql.

Daniel


>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > (table has ~30k rows)
 > > EXPLAIN SELECT * FROM r where d = 8;
 > > The result is 
 > > NOTICE: QUERY PLAN:
 > > Seq Scan on r (cost=0.00...3041.13 rows=7191 width=4)
 > 
 > Seqscan is the right plan to retrieve 7k rows out of a 30k table.
 > So the question is whether that estimate is in the right ballpark
 > or not.  How many rows are there really with d=8?  If it's way off,
 > what do you get from
 > 
 > select attname,attdispersion,s.*
 > from pg_statistic s, pg_attribute a, pg_class c
 > where starelid = c.oid and attrelid = c.oid and staattnum = attnum
 > and relname = 'r';
 > 
 > 			regards, tom lane



Mon, 03 Dec 2001 20:06:25 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: 7.1.3 not using index 
Daniel Kalchev <daniel@digsys.bg> writes:
> You may be correct that sequential scan is preferable, but I can never get 
> version 7.1.3 to use index scan on almost any table.

That's a fairly large claim to make, especially on the evidence of this
one table.


>      attname     | attdispersion | starelid | staattnum | staop | stanullfrac 
> | stacommonfrac | stacommonval | staloval | stahival
>  d               |      0.104507 |  8160023 |         4 |    97 |           0 
> |      0.257437 | 8            | 1        | 32

> In fact, field 'd' has only few values - usually powers of 2
(history).

What you've got here is that 8 is recorded as the most common value in
column d, with a frequency of 0.25 or about 1/4th of the table.  So
searches for d = 8 will correctly estimate the selectivity at about 0.25
and will (correctly) decide not to use the index.

7.1 does not have any info about column values other than the most
common, and will arbitrarily estimate their frequencies at (IIRC)
one-tenth of the most common value's.  That's probably still too much
to trigger an indexscan; the crossover point is usually 1% or even
less selectivity.

> Values are respectively 1,2,4,8. 16 and 32 and are spread like:

>  person_type | count 
> -------------+-------
>            1 |  8572
>            2 |  3464
>            4 |  8607
>            8 |  7191
>           16 |     3
>           32 |    96
> (6 rows)

7.2 will do better on this sort of example: it should correctly select
an indexscan when looking for 16 or 32, otherwise a seqscan.

> I also note very slow response to any queries that access systems
> tables, such as \d in psql.

There might indeed be something broken in your installation, but you've
shown me no concrete evidence of it so far.  On this query, 7.1 is
behaving as designed.

			regards, tom lane

Mon, 03 Dec 2001 13:19:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 
>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > You may be correct that sequential scan is preferable, but I can never get
      
 > > version 7.1.3 to use index scan on almost any table.
 > 
 > That's a fairly large claim to make, especially on the evidence of this
 > one table.

I tend to make it after waiting for almost two calendar days for an join query 
to complete (which takes at most under 10 seconds on 7.0). :-) (and of course, 
after spending few more days to understand what is going on)

 > >      attname     | attdispersion | starelid | staattnum | staop | stanullf
     rac 
 > > | stacommonfrac | stacommonval | staloval | stahival
 > >  d               |      0.104507 |  8160023 |         4 |    97 |         
       0 
 > > |      0.257437 | 8            | 1        | 32
 > 
 > > In fact, field 'd' has only few values - usually powers of 2
 > (history).
 > 
 > What you've got here is that 8 is recorded as the most common value in
 > column d, with a frequency of 0.25 or about 1/4th of the table.  So
 > searches for d = 8 will correctly estimate the selectivity at about 0.25
 > and will (correctly) decide not to use the index.

This I understand and this is why I gave the other examples... Your 
explanation on how 7.1 would handle this situation sort of explains the 
unfortunate siguation...

Am I correct in assuming that it will be better to delete the index on such 
fields? (for 7.1)

 > > I also note very slow response to any queries that access systems
 > > tables, such as \d in psql.
 > 
 > There might indeed be something broken in your installation, but you've
 > shown me no concrete evidence of it so far.  On this query, 7.1 is
 > behaving as designed.

If you are going to tell me 7.1 will only use index scan on PRIMARY KEY 
columns, I will spend some more time with the 7.2 betas (who knows, this may 
be the secret plan <grin>)

Here is another table:

CREATE TABLE "persons" (
        "personid" integer DEFAULT nextval('personid_seq'::text),
        "name" text,
        "title" text,
[...]
);

CREATE  INDEX "persons_personid_idx" on "persons" using btree ( "personid" 
"int4_ops" );

db=# select count(*) from persons;

 count 
-------
 14530
(1 row)

(part of the statistics for this row)
   attname   | attdispersion | starelid | staattnum | staop | stanullfrac | 
stacommonfrac |      stacommonval      |        staloval        |         
stahival
-------------+---------------+----------+-----------+-------+-------------+----
-----------+------------------------+------------------------+-----------------
---------
 personid    |    4.1328e-05 |    19795 |         1 |    97 |           0 |   
0.000206469 | 2089                   | 1                      | 12857

now, EXPLAIN again gives me:

db=# explain select * from persons where personid = 1;
NOTICE:  QUERY PLAN:

Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)

(note, personid is not unique - there are some 'duplicate' rows that mark 
archived records - but there are no more than 4-5 occurrences of the same 
personid and this is rare)

If this is problem with my installation (I especially installed new BSD/OS 4.2 
to test on clean 7.1.3 with my production database). It has locale eanbled, 
but nowhere in the queries there is text involved...

How about this query (using my previous table r, that has poiner to the 
personid on persons):

db=# explain select * from persons, r where r.d = 1 and r.a = persons.personid;
NOTICE:  QUERY PLAN:

Merge Join  (cost=0.00..nan rows=299 width=193)
  ->  Index Scan using persons_personid_idx on persons  (cost=0.00..nan 
rows=14530 width=177)
  ->  Index Scan using r_a_idx on representatives  (cost=0.00..nan rows=719 
width=16)

Why would it do index scans on r.a? 

Daniel


Mon, 03 Dec 2001 20:38:19 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: 7.1.3 not using index 
Daniel Kalchev <daniel@digsys.bg> writes:
> Here is another table:

> CREATE TABLE "persons" (
>         "personid" integer DEFAULT nextval('personid_seq'::text),
>         "name" text,
>         "title" text,
> [...]
> );

> CREATE  INDEX "persons_personid_idx" on "persons" using btree ( "personid" 
> "int4_ops" );

> (part of the statistics for this row)
>    attname   | attdispersion | starelid | staattnum | staop | stanullfrac | 
> stacommonfrac |      stacommonval      |        staloval        |         
> stahival
>  personid    |    4.1328e-05 |    19795 |         1 |    97 |           0 |   
> 0.000206469 | 2089                   | 1                      | 12857

> now, EXPLAIN again gives me:

> db=# explain select * from persons where personid = 1;
> NOTICE:  QUERY PLAN:

> Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)

That does seem pretty broken; the thing is well aware that the query is
selective (note the rows estimate), so why is it not using the index?

Do you get the same plan if you try to force an indexscan by doing
	set enable_seqscan to off;

Also, I'd like to see the EXPLAIN VERBOSE result not just EXPLAIN.

> db=# explain select * from persons, r where r.d = 1 and r.a = persons.personid;
> NOTICE:  QUERY PLAN:

> Merge Join  (cost=0.00..nan rows=299 width=193)
>   ->  Index Scan using persons_personid_idx on persons  (cost=0.00..nan 
> rows=14530 width=177)
>   ->  Index Scan using r_a_idx on representatives  (cost=0.00..nan rows=719 
> width=16)

> Why would it do index scans on r.a? 

To get the data in the right order for a merge join.  However, I think
the really interesting part of this is the "cost=0.00..nan" bit.
Apparently you're getting some NaN results during computation of the
cost estimates, which will completely screw up all the planner's
estimates of which plan is cheapest.  That needs to be looked at.
We've seen previous reports of 7.1 getting confused that way when there
were column min or max values of +/-infinity in timestamp columns ...
but it looks like these are plain integer columns, so there's something
else going on.

One thing that should be eliminated at the outset is the possibility of
a bad build of Postgres.  How did you configure and build, *exactly*?
Did you make any midcourse corrections (like building some of the files
with different compiler switches than others)?

			regards, tom lane

Mon, 03 Dec 2001 14:57:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 
>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > Here is another table:
 > 
 > > CREATE TABLE "persons" (
 > >         "personid" integer DEFAULT nextval('personid_seq'::text),
 > >         "name" text,
 > >         "title" text,
 > > [...]
 > > );
 > 
 > > CREATE  INDEX "persons_personid_idx" on "persons" using btree ( "personid"
      
 > > "int4_ops" );
 > 
 > > (part of the statistics for this row)
 > >    attname   | attdispersion | starelid | staattnum | staop | stanullfrac 
     | 
 > > stacommonfrac |      stacommonval      |        staloval        |         
 > > stahival
 > >  personid    |    4.1328e-05 |    19795 |         1 |    97 |           0 
     |   
 > > 0.000206469 | 2089                   | 1                      | 12857
 > 
 > > now, EXPLAIN again gives me:
 > 
 > > db=# explain select * from persons where personid = 1;
 > > NOTICE:  QUERY PLAN:
 > 
 > > Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)
 > 
 > That does seem pretty broken; the thing is well aware that the query is
 > selective (note the rows estimate), so why is it not using the index?
 > 
 > Do you get the same plan if you try to force an indexscan by doing
 > 	set enable_seqscan to off;

Here is what it gives:

db=# set enable_seqscan to off;
SET VARIABLE
db=# explain select * from persons where personid = 1;
NOTICE:  QUERY PLAN:

Index Scan using persons_personid_idx on persons  (cost=0.00..nan rows=1 
width=177)


 > 
 > Also, I'd like to see the EXPLAIN VERBOSE result not just EXPLAIN.

Here it is (after turning enable_seqscan back on)


db=# explain verbose select * from persons where personid = 1;
NOTICE:  QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 490.62 :rows 1 :width 177 
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod 
-1 :resname personid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 
:restypmod -1 :resname name :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk 
false } :expr { VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM 
:resno 3 :restype 25 :restypmod -1 :resname title :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 
25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY 
:resdom { RESDOM :resno 4 :restype 25 :restypmod -1 :resname occupation 
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 
:varattno 4 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 
4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname 
person_type :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod 
-1 :resname street :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 25 
:restypmod -1 :resname town :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk 
false } :expr { VAR :varno 1 :varattno 7 :vartype 25 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 7}} { TARGETENTRY :resdom { RESDOM 
:resno 8 :restype 25 :restypmod -1 :resname zipcode :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 
25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY 
:resdom { RESDOM :resno 9 :restype 25 :restypmod -1 :resname phone :reskey 0 
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 
9 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 9}} { 
TARGETENTRY :resdom { RESDOM :resno 10 :restype 25 :restypmod -1 :resname fax 
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 
:varattno 10 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 
10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 25 :restypmod -1 
:resname email :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr 
{ VAR :varno 1 :varattno 11 :vartype 25 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 
16 :restypmod -1 :resname archived :reskey 0 :reskeyop 0 :ressortgroupref 0 
:resjunk false } :expr { VAR :varno 1 :varattno 12 :vartype 16 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom { RESDOM 
:resno 13 :restype 1043 :restypmod 20 :resname archived_by :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 13 
:vartype 1043 :vartypmod 20  :varlevelsup 0 :varnoold 1 :varoattno 13}} { 
TARGETENTRY :resdom { RESDOM :resno 14 :restype 1184 :restypmod -1 :resname 
archived_at :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 14 :vartype 1184 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 14}} { TARGETENTRY :resdom { RESDOM :resno 15 :restype 
1184 :restypmod -1 :resname created_at :reskey 0 :reskeyop 0 :ressortgroupref 
0 :resjunk false } :expr { VAR :varno 1 :varattno 15 :vartype 1184 :vartypmod 
-1  :varlevelsup 0 :varnoold 1 :varoattno 15}} { TARGETENTRY :resdom { RESDOM 
:resno 16 :restype 1043 :restypmod 20 :resname created_by :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 16 
:vartype 1043 :vartypmod 20  :varlevelsup 0 :varnoold 1 :varoattno 16}} { 
TARGETENTRY :resdom { RESDOM :resno 17 :restype 1184 :restypmod -1 :resname 
updated_at :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 17 :vartype 1184 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 17}} { TARGETENTRY :resdom { RESDOM :resno 18 :restype 
1043 :restypmod 20 :resname updated_by :reskey 0 :reskeyop 0 :ressortgroupref 
0 :resjunk false } :expr { VAR :varno 1 :varattno 18 :vartype 1043 :vartypmod 
20  :varlevelsup 0 :varnoold 1 :varoattno 18}}) :qpqual ({ EXPR :typeOid 16  
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR 
:varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull 
false :constvalue  4 [ 1 0 0 0 ] })}) :lefttree <> :righttree <> :extprm () 
:locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)

> One thing that should be eliminated at the outset is the possibility of
 > a bad build of Postgres.  How did you configure and build, *exactly*?
 > Did you make any midcourse corrections (like building some of the files
 > with different compiler switches than others)?


I will rebuild it again, re-initdb and reload the whole database, but this 
build was on vanilla BSD/OS 4.2 with the only modifications to add larger 
shared memory support in the kernel (I need to run many backends). My current 
favorite (which I copy from server to server :) is

# support for larger processes and number of childs
options         "DFLDSIZ=\(128*1024*1024\)"
options         "MAXDSIZ=\(256*1024*1024\)"   
options         "CHILD_MAX=256"
options         "OPEN_MAX=256"
options         "KMAPENTRIES=4000" # Prevents kmem malloc errors !
options         "KMEMSIZE=\(32*1024*1024\)"

options         "SHMMAXPGS=32768"
options         "SHMMNI=400"
options         "SHMSEG=204"
# More semaphores for Postgres
options         "SEMMNS=600"

PostgreSQL was build with these options

./configure  --enable-locale --with-perl --with-pythos --with-tcl 
--enable-obdc --with-krb4 --with-openssl --enable-syslog 
--with-includes=/usr/include/kerberosIV:/usr/contrib/include

I have the habbit to always clean before every build.

What I will do try to do now is to clean/rebuild/install everything again. 
Then try to build with --enable-locale only. Then try to build without any 
options at all..

Hope you find some useful information to track this down.

Daniel


Mon, 03 Dec 2001 22:57:59 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: 7.1.3 not using index 
Daniel Kalchev <daniel@digsys.bg> writes:
>>> Do you get the same plan if you try to force an indexscan by doing
>>> set enable_seqscan to off;

> db=# set enable_seqscan to off;
> SET VARIABLE
> db=# explain select * from persons where personid = 1;
> NOTICE:  QUERY PLAN:

> Index Scan using persons_personid_idx on persons  (cost=0.00..nan rows=1 
> width=177)

Hmph.  The evidence so far suggests that you're getting a NaN cost
estimate for *any* indexscan, ie, the problem is somewhere in cost_index
or its subroutines.  That's a bit of a leap but it's consistent both
with your general complaint and these specific examples.

> I will rebuild it again, re-initdb and reload the whole database, but this 
> build was on vanilla BSD/OS 4.2 with the only modifications to add larger 
> shared memory support in the kernel (I need to run many backends).

I'd wonder more about your compiler than the kernel.  Keep in mind that
no one but you has reported anything like this ... so there's got to be
some fairly specific cause.

> PostgreSQL was build with these options

> ./configure  --enable-locale --with-perl --with-pythos --with-tcl 
> --enable-obdc --with-krb4 --with-openssl --enable-syslog 
> --with-includes=/usr/include/kerberosIV:/usr/contrib/include

> What I will do try to do now is to clean/rebuild/install everything again. 
> Then try to build with --enable-locale only.

Offhand I would not expect any of those options to affect anything
happening in the planner, at least not for integer column types.

Wild guess: what is configure producing for the ALIGN_xxx macros?
(look in src/include/config.h)  Does it match what you believe about
your hardware?

			regards, tom lane

Mon, 03 Dec 2001 16:05:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 
>>>Tom Lane said:
 > > I will rebuild it again, re-initdb and reload the whole database, but this
      
 > > build was on vanilla BSD/OS 4.2 with the only modifications to add larger 
 > > shared memory support in the kernel (I need to run many backends).
 > 
 > I'd wonder more about your compiler than the kernel.  Keep in mind that
 > no one but you has reported anything like this ... so there's got to be
 > some fairly specific cause.

Well... the same compiler (machine) happily runs 7.0.3. But see below.

 > > PostgreSQL was build with these options
 > 
 > > ./configure  --enable-locale --with-perl --with-pythos --with-tcl 
 > > --enable-obdc --with-krb4 --with-openssl --enable-syslog 
 > > --with-includes=/usr/include/kerberosIV:/usr/contrib/include
 > 
 > > What I will do try to do now is to clean/rebuild/install everything again.
      
 > > Then try to build with --enable-locale only.
 > 
 > Offhand I would not expect any of those options to affect anything
 > happening in the planner, at least not for integer column types.

It did not change anything, when I just 

make clean
make
make install

It WORKED however after

make clean
rm config.cache
./configure  --enable-locale --with-perl
make
make install

You will say, that configure had mangled things up... But I then tried:

make clean
rm config.cache
./configure (with the all options)
make
make install

and got the same junk!

Then, I discovered that my configure options had two typos.... corrected these 
and now all works!?!?!?!

How is this possible? Why would not configure complain for incorrect options?

 > Wild guess: what is configure producing for the ALIGN_xxx macros?
 > (look in src/include/config.h)  Does it match what you believe about
 > your hardware?

Looks very reasonable, as far as I can tell:

#define ALIGNOF_SHORT 2
#define ALIGNOF_INT 4
#define ALIGNOF_LONG 4
#define ALIGNOF_LONG_LONG_INT 4
#define ALIGNOF_DOUBLE 4
#define MAXIMUM_ALIGNOF 4


It may turn to be some library trouble...

Daniel


Mon, 03 Dec 2001 23:47:06 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: 7.1.3 not using index 
By the way, now that it works, I am glad to prove you wrong on the optimizer 
behavior on 7.1.3 :-)

My query 

select * from r where d = 8; 

still results in sequential scan:

Seq Scan on r  (cost=0.00..527.16 rows=7191 width=16)

However, the query

select * from r where d = 1; 

now results in index scan.

Index Scan using r_d_idx on r  (cost=0.00..308.45 rows=719 width=16)

Not to say I am sufficiently confused - now to go on with some more testing...

Daniel


Mon, 03 Dec 2001 23:51:21 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: 7.1.3 not using index 
Daniel Kalchev <daniel@digsys.bg> writes:
> Then, I discovered that my configure options had two
> typos.... corrected these and now all works!?!?!?!

My, *that's* interesting.  configure is supposed to ignore unrecognized
--with and --enable options.

> How is this possible? Why would not configure complain for incorrect options?

The autoconf people claim that's a feature.  I think it's a bug, too,
but our opinion doesn't count.

> It may turn to be some library trouble...

I'm wondering the same.  Try saving the make log for both ways of
configuring, and comparing to see if there's any difference in what
libraries get linked into the backend.

			regards, tom lane

Mon, 03 Dec 2001 17:12:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 
>> It may turn to be some library trouble...

After examining the code a little, I wonder whether the problem might
be due to some library messing up the behavior of log().  You could
experiment at the SQL level with "SELECT ln(x)" to see if there's
anything obviously wrong.

BTW, do the regression tests show any difference in behavior between
the good and bad builds?

			regards, tom lane

Mon, 03 Dec 2001 17:53:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1.3 not using index 