RE: Using BOOL in indexes

> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue(at)tpf(dot)co(dot)jp]
> > -----Original Message-----
> > From: pgsql-hackers-owner(at)hub(dot)org [mailto:pgsql-hackers-owner(at)hub(dot)org]On
> > Behalf Of Andrew McMillan
> >
> > Hi,
> >
> > I'm trying to convert an application from MS SQL / ASP / IIS to
> > PostgreSQL / PHP / Apache. I am having trouble getting efficient
> > queries on one of my main tables, which tends to have some fairly large
> > records in it. Currently there are around 20000 records, and it looks
> > like they average around 500 bytes from the VACUUM ANALYZE statistics
> > below.
> >
> > I don't really want any query on this table to return more than about 20
> > records, so it seems to me that indexed access should be the answer, but
> > I am having some problems with indexes containing BOOLEAN types.
> >
> > I can't see any reason why BOOL shouldn't work in an index, and in other
> > systems I have commonly used them as the first component of an index,
> > which is what I want to do here.
> >
> > Also, I can't see why the estimator should see a difference between
> > "WHERE head1" and "WHERE head1=TRUE".
> >
> >
> > newsroom=# explain SELECT DISTINCT story.story_id, written, released,
> > title, precis, author, head1 FROM story WHERE head1 ORDER BY written
>
> Please add head1 to ORDER BY clause i.e. ORDER BY head1,written.
>

Sorry,it wouldn't help unless there's an index e.g. on (head1,written,
story_id, released, title, precis, author).
However isn't (story_id) a primary key ?
If so,couldn't you change your query as follows ?

SELECT story.story_id, written, released, title, precis, author, head1
FROM story WHERE head1=TRUE ORDER BY head1, written DESC
LIMIT 15.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp