Lack of use of indexes

On Fri, 22 Nov 2002, Don Isgitt wrote:

Thanks, Doug for your very prompt reply. This newsgroup is wonderful.

It will take a while, but I will create the table with text fields to
see if that helps. The query with ...section='14' did not use the index.

Note that you can also "cast" the field you're searching on...

select * from sometable where field='14'::char(5)

Don Isgitt <djisgitt@soundenergy.com> writes:

gds2=# select count(*) from lg;
count
--------
138459
(1 row)

gds2=# explain select * from lg where state='NM';
NOTICE: QUERY PLAN:

Seq Scan on lg (cost=0.00..5885.77 rows=14890 width=73)

An indexscan is usually a poor choice for retrieving 10% of the data in
a table (unless the index order and physical order are highly
correlated, as for instance after a CLUSTER command).

If you don't think the planner is guessing correctly here, you can force
an indexscan (do "set enable_seqscan = off") ... but I'll bet it gets
slower.

For randomly-ordered rows the cutoff point for indexscan effectiveness
is surprisingly low --- typically around 1% of the rows.

regards, tom lane

Thanks, Doug for your very prompt reply. This newsgroup is wonderful.

It will take a while, but I will create the table with text fields to
see if that helps. The query with ...section='14' did not use the index.

Don

Doug McNaught wrote:

Don Isgitt <djisgitt@soundenergy.com> writes:

gds2=# explain select * from lg where section=14;
NOTICE: QUERY PLAN:

Seq Scan on lg (cost=0.00..5885.77 rows=3836 width=73)

EXPLAIN
gds2=# set enable_seqscan=off;
SET VARIABLE
gds2=# explain select * from lg where section=14;
NOTICE: QUERY PLAN:

Index Scan using lgsec on lg (cost=0.00..12167.45 rows=3836 width=73)

EXPLAIN

Of course, the above only proves that the planner thinks the indexscan
will be slower ;-). You should try EXPLAIN ANALYZE to see how well the
planner estimates square up with reality ...

regards, tom lane

Thank you, Tom, for your reply.

As usual (from my observation of this newsgroup), you are quite correct,
as was Stephan. To wit,

gds2=# explain select * from lg where section=14;
NOTICE: QUERY PLAN:

Seq Scan on lg (cost=0.00..5885.77 rows=3836 width=73)

EXPLAIN
gds2=# set enable_seqscan=off;
SET VARIABLE
gds2=# explain select * from lg where section=14;
NOTICE: QUERY PLAN:

Index Scan using lgsec on lg (cost=0.00..12167.45 rows=3836 width=73)

EXPLAIN

I am surprised at the low cutoff percentage, but it is nice to know for
future reference.

Thank you also to Doug and Stephan for your help. I remain quite
pleasantly amazed at the quality of the software and of the support.

Don

Tom Lane wrote:

Don Isgitt <djisgitt@soundenergy.com> writes:

Smart planner...curiosity: what is that first number following cost= and
actual time=?

Startup time (effectively, the time to return the first row).

regards, tom lane

Quite so! Therefore,

gds2=# explain analyze select boundary from lg where section=14;
NOTICE: QUERY PLAN:

Index Scan using lgsec on lg (cost=0.00..12167.45 rows=3836 width=32)
(actual time=44.98..18325.87 rows=3759 loops=1)
Total runtime: 18344.06 msec

EXPLAIN
gds2=# set enable_seqscan=on;
SET VARIABLE
gds2=# explain analyze select boundary from lg where section=14;
NOTICE: QUERY PLAN:

Seq Scan on lg (cost=0.00..5885.77 rows=3836 width=32) (actual
time=0.24..2974.65 rows=3759 loops=1)
Total runtime: 2987.61 msec

EXPLAIN

Smart planner...curiosity: what is that first number following cost= and
actual time=?

Thank you again.
Don

Tom Lane wrote: