RE: Using BOOL in indexes

> -----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.

Regards.

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