Using BOOL in indexes - Mailing list pgsql-hackers

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

Any help appreciated,                Andrew.


newsroom=# \d story               Table "story" Attribute   |   Type    |     Modifier      
--------------+-----------+-------------------story_id     | integer   | not nullauthor       | integer   | written
| timestamp | released     | timestamp | withdrawn    | timestamp | sent         | timestamp | wcount       | integer
|default 0chunk_count  | integer   | head1        | boolean   | default 'f'::boolheadpriority | integer   | default
999internal    | boolean   | default 'f'::boolislive       | boolean   | default 'f'::boolstory_type   | char(4)   |
title       | text      | precis       | text      | 
 
Indices: story_oid_skey,        story_pkey,        story_sk1,        story_sk2,        story_sk4

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

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

Unique  (cost=2623.87..2868.99 rows=1401 width=49) ->  Sort  (cost=2623.87..2623.87 rows=14007 width=49)       ->  Seq
Scanon story  (cost=0.00..1421.57 rows=14007 width=49)
 

EXPLAIN

newsroom=# set enable_seqscan to 'off';
SET VARIABLE

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

Unique  (cost=100002623.87..100002868.99 rows=1401 width=49) ->  Sort  (cost=100002623.87..100002623.87 rows=14007
width=49)      ->  Seq Scan on story  (cost=100000000.00..100001421.57
 
rows=14007 width=49)

EXPLAIN

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

Unique  (cost=8846.22..9056.33 rows=1401 width=48) ->  Sort  (cost=8846.22..8846.22 rows=14007 width=48)       ->
IndexScan using story_sk4 on story  (cost=0.00..7645.97
 
rows=14007 width=48)

EXPLAIN

newsroom=# vacuum verbose analyze story;
NOTICE:  --Relation story--
NOTICE:  Pages 1238: Changed 0, reaped 0, Empty 0, New 0; Tup 18357: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 84, MaxLen 3115; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.16s/1.90u sec.
NOTICE:  Index story_oid_skey: Pages 39; Tuples 18357. CPU 0.00s/0.07u
sec.
NOTICE:  Index story_sk4: Pages 94; Tuples 18357. CPU 0.01s/0.08u sec.
NOTICE:  Index story_sk2: Pages 51; Tuples 18357. CPU 0.01s/0.08u sec.
NOTICE:  Index story_sk1: Pages 70; Tuples 18357. CPU 0.02s/0.06u sec.
NOTICE:  Index story_pkey: Pages 59; Tuples 18357. CPU 0.02s/0.06u sec.
VACUUM


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