> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> > -----Original Message-----
> > From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.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@tpf.co.jp


Wed, 31 May 2000 12:04:41 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
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@cat-it.co.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

Wed, 31 May 2000 16:01:15 +1200
Andrew McMillan <Andrew@catalyst.net.nz>
Re: Using BOOL in indexes
> -----Original Message-----
> From: andrew@socrates.catalyst.net.nz
> 
> Hiroshi Inoue wrote:
> > Hiroshi Inoue wrote:
> > > Andrew McMillan wrote:
> > > >
> > > > Hi,
> > > >
> > 
> > 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;

Oops,please add DESC to head1 also i.e ORDER BY head1 DESC,written DESC.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Wed, 31 May 2000 13:21:32 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Using BOOL in indexes