Questions on 7.2.1 query plan choices

From: Ed Loehr <pggeneral(at)bluepolka(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Questions on 7.2.1 query plan choices
Date: 2002-04-17 22:15:01
Message-ID: 3CBDF3E5.6050808@bluepolka.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This seems pretty basic...I'd appreciate someone showing me the error of my
ways...Questions below this schema...

$ psql -c "\d freetext"
Table "freetext"
Column | Type | Modifiers
------------------+-----------------------+----------------------------
value | text |
key | integer | not null
isindexed | boolean | not null default 'f'::bool
tobeindexed | boolean | default 'f'::bool
Indexes: indexed_idx
Unique keys: freetext_pkey

$ psql -c "\d indexed_idx"
Index "indexed_idx"
Column | Type
-------------+---------
tobeindexed | boolean
isindexed | boolean
btree

$ psql -c "\d freetext_pkey"
Index "freetext_pkey"
Column | Type
--------+---------
key | integer
unique btree

1) There are over 700,000 rows in the table below, but only about 1,300
matching the where clause. How can I (and should I) get the planner to
choose to traverse indexed_idx instead of a sequential scan? The following
is immediately after calling 'analyze'...

$ psql -c "explain select key, value from freetext where tobeindexed = 't'
and isindexed = 'f'
NOTICE: QUERY PLAN:

Seq Scan on freetext (cost=0.00..102114.21 rows=296161 width=1138)

$ psql -c "select count(key) from freetext"
count
--------
728868
(1 row)

$ psql -c "select count(key) from freetext where tobeindexed = 't' and
isindexed = 'f'"
count
-------
1319
(1 row)

2) Why does the planner choose to first scan freetext_pkey when choosing
indexed_idx would narrow the 700K rows down to 1300 in the query below? As
it is, it is apparently doing the equivalent of a backward seqscan of 700K
rows right of the bat.

$ psql -c "explain select key, value from freetext where tobeindexed = 't'
and isindexed = 'f' order by key desc limit 25;
NOTICE: QUERY PLAN:

Limit (cost=0.00..267.87 rows=25 width=1144)
-> Index Scan Backward using freetext_pkey on freetext
(cost=0.00..3165306.12 rows=295414 width=1144)

-Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Post 2002-04-17 22:35:34 PERL DBI and Novell Netware
Previous Message Bruce Momjian 2002-04-17 21:58:31 Re: [SQL] A bug in gistPageAddItem()/gist_tuple_replacekey() ???