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