Обсуждение: take my index, please

greetings, data mutilators,

postgres 7.1, redhat 6.1

I've read a few recent threads about the planner not choosing the
indexes people would like, but my situation seems extraordinary
because I don't even get an index scan on the PRIMARY KEY, even when I
set enable_seqscan to off!

The query is as simple as select a from b where c = n;

I would be very grateful if anyone could explain (heh) to me why the
sequential scan is preferred because this exercise is not academic.

here are some details:

webco=# \d allocation
                  Table "allocation"
   Attribute    |           Type           | Modifier
----------------+--------------------------+----------
 allocation_oid | bigint                   | not null
 state          | character varying        |
 location_oid   | bigint                   |
 agent_oid      | bigint                   |
 patron_oid     | bigint                   |
 creation_time  | timestamp with time zone |
 pickup_time    | timestamp with time zone |
 return_time    | timestamp with time zone |
 summary        | character varying        |
 schedule_rule  | character varying        |
 resource_rule  | character varying        |
Index: allocationpk

webco=# \d allocationpk
  Index "allocationpk"
   Attribute    |  Type
----------------+--------
 allocation_oid | bigint
unique btree

webco=# explain select * from allocation where allocation_oid = 5;
NOTICE:  QUERY PLAN:

Seq Scan on allocation  (cost=0.00..2295.79 rows=1 width=104)

EXPLAIN
webco=# set enable_seqscan TO OFF;
SET VARIABLE
webco=# explain select * from allocation where allocation_oid = 5;
NOTICE:  QUERY PLAN:

Seq Scan on allocation  (cost=100000000.00..100002295.79 rows=1 width=104)

EXPLAIN

indeed.

-Lyn

I get past configure and then run gmake.  It starts and then begins looking for files by backup 4 directory levels?  It
willlook for a few dozen .h files before dying.  What am I leaving out? 

Thanks.
Stephen

bullwinkle.eng.auburn.edu{sford}44: gmake
gmake -C doc all
gmake[1]: Entering directory `/home/ens/sford/postgre/postgresql-7.1/doc'
gmake[1]: Nothing to be done for `all'.
gmake[1]: Leaving directory `/home/ens/sford/postgre/postgresql-7.1/doc'
gmake -C src all
gmake[1]: Entering directory `/home/ens/sford/postgre/postgresql-7.1/src'
gmake -C backend all
gmake[2]: Entering directory `/home/ens/sford/postgre/postgresql-7.1/src/backend'
gmake -C access all
gmake[3]: Entering directory `/home/ens/sford/postgre/postgresql-7.1/src/backend/access'
gmake -C common SUBSYS.o
gmake[4]: Entering directory `/home/ens/sford/postgre/postgresql-7.1/src/backend/access/common'
gcc  -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include   -c -o heaptuple.o heaptuple.c
In file included from ../../../../src/include/postgres.h:48,
                 from heaptuple.c:21:
../../../../src/include/c.h:54: stdio.h: No such file or directory
../../../../src/include/c.h:55: stdlib.h: No such file or directory
../../../../src/include/c.h:56: string.h: No such file or directory
In file included from heaptuple.c:23:
../../../../src/include/access/heapam.h:17: time.h: No such file or directory
In file included from ../../../../src/include/storage/lock.h:17,
                 from ../../../../src/include/storage/bufmgr.h:19,
                 from ../../../../src/include/storage/bufpage.h:18,
                 from ../../../../src/include/access/htup.h:17,
                 from ../../../../src/include/access/heapam.h:18,
                 from heaptuple.c:23:
../../../../src/include/storage/ipc.h:21: sys/types.h: No such file or directory
In file included from /opt/gnu/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/include/syslimits.h:7,
                 from /opt/gnu/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/include/limits.h:11,
                 from ../../../../src/include/utils/nabstime.h:17,
                 from ../../../../src/include/access/xact.h:19,
                 from ../../../../src/include/utils/tqual.h:19,
                 from ../../../../src/include/access/relscan.h:17,
                 from ../../../../src/include/access/heapam.h:19,
                 from heaptuple.c:23:
/opt/gnu/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/include/limits.h:117: limits.h: No such file or directory
In file included from ../../../../src/include/access/xact.h:19,
                 from ../../../../src/include/utils/tqual.h:19,
                 from ../../../../src/include/access/relscan.h:17,
                 from ../../../../src/include/access/heapam.h:19,
                 from heaptuple.c:23:
../../../../src/include/utils/nabstime.h:18: time.h: No such file or directory
In file included from ../../../../src/include/utils/nabstime.h:21,
                 from ../../../../src/include/access/xact.h:19,
                 from ../../../../src/include/utils/tqual.h:19,
                 from ../../../../src/include/access/relscan.h:17,

Lyn A Headley <laheadle@cs.uchicago.edu> writes:
>                   Table "allocation"
>    Attribute    |           Type           | Modifier
> ----------------+--------------------------+----------
>  allocation_oid | bigint                   | not null

> webco=# explain select * from allocation where allocation_oid = 5;
> NOTICE:  QUERY PLAN:

> Seq Scan on allocation  (cost=0.00..2295.79 rows=1 width=104)

Try

select * from allocation where allocation_oid = 5::int8;

As you wrote it, it's a cross-datatype operator (int8 vs int4)
and the planner is not very smart about those at the moment.

You should probably also ask yourself whether you *really* need int8
for this column, at least for the next release or two that it's likely
to be before something is done about this problem.

            regards, tom lane