Re: Using BOOL in indexes

From: Andrew McMillan <Andrew(at)catalyst(dot)net(dot)nz>
To: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using BOOL in indexes
Date: 2000-05-31 04:01:15
Message-ID: 39348E8B.1684188A@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hiroshi Inoue 2000-05-31 04:21:32 RE: Using BOOL in indexes
Previous Message Tom Lane 2000-05-31 03:53:18 Re: Applying TOAST to CURRENT