Re: Using BOOL in indexes

Hiroshi Inoue wrote:
> Hiroshi Inoue wrote:
> > Andrew McMillan wrote:
> > >
> > > 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.

Thanks Hiroshi,

I already have such an index, but as you can see below, it is still not
used:

newsroom=# explain SELECT story.story_id, written, released, title,
precis, author, head1 FROM story WHERE head1=TRUE ORDER BY head1,
written DESC LIMIT 15;
NOTICE: QUERY PLAN:

Sort (cost=2669.76..2669.76 rows=14007 width=49)
-> Seq Scan on story (cost=0.00..1467.46 rows=14007 width=49)

EXPLAIN
newsroom=# \d story_sk4
Index "story_sk4"
Attribute | Type
-----------+-----------
head1 | boolean
written | timestamp
btree

Regards,
Andrew.

--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267