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
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() ??? |