Re: CREATE INDEX spoils IndexScan planns

Hi Rod,

here is the actual production trace of the problem.
This is a table of mobile network cells and code-names devided into LAC's.

Two test cells to test with are:
test cell id #1: 900 4900035
test cell id #2: 300 5080140

You were right, the volume of the data changes the optimizer's
willingness to use indexes.
Another funny thing though... I actually did two tests. One with the
actual production
data and the other one using only a slice of that (just 1000 rows). On
the second smaller
test the optimizer insisted to go with the SeqScan eventhou IndexScan
(after forcing it)
turned out to be about 10 times faster. Here are the both traces...

Many thanks for your effort,

Kind regards,

Vince

TRACE #1 (the big one):
-----------------------------------------------------------------------------------------------------------------------
Welcome to psql 7.4beta5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

stats=# select count(*) from omc_cell;
count
-------
42843
(1 row)

stats=# show enable_seqscan;
enable_seqscan
----------------
on
(1 row)

stats=# explain analyze select * from omc_cell where lac = '900' and
cellid = '4900035';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..4.83 rows=1
width=72) (actual time=0.177..0.194 rows=1 loops=1)
Index Cond: (((lac)::text = '900'::text) AND ((cellid)::text =
'4900035'::text))
Total runtime: 0.604 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '300' and
cellid = '5080140';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..4.83 rows=1
width=72) (actual time=0.176..0.194 rows=1 loops=1)
Index Cond: (((lac)::text = '300'::text) AND ((cellid)::text =
'5080140'::text))
Total runtime: 0.541 ms
(3 rows)

stats=# analyze;
ANALYZE

stats=# explain analyze select * from omc_cell where lac = '900' and
cellid = '4900035';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..5.00 rows=1
width=34) (actual time=0.175..0.194 rows=1 loops=1)
Index Cond: (((lac)::text = '900'::text) AND ((cellid)::text =
'4900035'::text))
Total runtime: 2.044 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '300' and
cellid = '5080140';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..5.00 rows=1
width=34) (actual time=0.179..0.197 rows=1 loops=1)
Index Cond: (((lac)::text = '300'::text) AND ((cellid)::text =
'5080140'::text))
Total runtime: 0.549 ms
(3 rows)

stats=# set enable_seqscan to off;
SET

stats=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)

stats=# analyze;
ANALYZE

stats=# explain analyze select * from omc_cell where lac = '900' and
cellid = '4900035';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..5.00 rows=1
width=34) (actual time=0.173..0.192 rows=1 loops=1)
Index Cond: (((lac)::text = '900'::text) AND ((cellid)::text =
'4900035'::text))
Total runtime: 1.954 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '300' and
cellid = '5080140';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..5.00 rows=1
width=34) (actual time=0.173..0.191 rows=1 loops=1)
Index Cond: (((lac)::text = '300'::text) AND ((cellid)::text =
'5080140'::text))
Total runtime: 0.544 ms
(3 rows)

stats=# create index test_x on omc_cell(cellid);
CREATE INDEX
stats=# explain analyze select * from omc_cell where lac = '900' and
cellid = '4900035';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..6.00 rows=1
width=34) (actual time=31.507..31.533 rows=1 loops=1)
Index Cond: (((lac)::text = '900'::text) AND ((cellid)::text =
'4900035'::text))
Total runtime: 31.899 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '300' and
cellid = '5080140';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..6.00 rows=1
width=34) (actual time=0.175..0.193 rows=1 loops=1)
Index Cond: (((lac)::text = '300'::text) AND ((cellid)::text =
'5080140'::text))
Total runtime: 0.550 ms
(3 rows)

stats=# explain analyze select * from omc_cell where cellid = '4900035';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using test_x on omc_cell (cost=0.00..6.01 rows=1 width=34)
(actual time=0.156..0.175 rows=1 loops=1)
Index Cond: ((cellid)::text = '4900035'::text)
Total runtime: 0.524 ms
(3 rows)

TRACE #2 (the small, funny one)
------------------------------------------------------------------------------------------------------------------
Welcome to psql 7.4beta5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

stats=# explain analyze select * from omc_cell where lac = '500';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..13.32 rows=4
width=72) (actual time=0.345..2.941 rows=150 loops=1)
Index Cond: ((lac)::text = '500'::text)
Total runtime: 3.976 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '600';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..13.32 rows=4
width=72) (actual time=0.136..1.112 rows=61 loops=1)
Index Cond: ((lac)::text = '600'::text)
Total runtime: 1.714 ms
(3 rows)

stats=# show enable_seqscan;
enable_seqscan
----------------
on
(1 row)

stats=# analyze;
ANALYZE
stats=# explain analyze select * from omc_cell where lac = '500';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on omc_cell (cost=0.00..14.80 rows=150 width=40) (actual
time=5.330..8.302 rows=150 loops=1)
Filter: ((lac)::text = '500'::text)
Total runtime: 10.810 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '600';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on omc_cell (cost=0.00..14.80 rows=62 width=40) (actual
time=6.599..7.624 rows=61 loops=1)
Filter: ((lac)::text = '600'::text)
Total runtime: 8.142 ms
(3 rows)

stats=# set enable_seqscan to off;
SET
stats=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)

stats=# analyze;
ANALYZE
stats=# explain analyze select * from omc_cell where lac = '500';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..21.05 rows=150
width=40) (actual time=0.133..2.711 rows=150 loops=1)
Index Cond: ((lac)::text = '500'::text)
Total runtime: 4.999 ms
(3 rows)

stats=# explain analyze select * from omc_cell where lac = '600';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_omc_cell on omc_cell (cost=0.00..19.85 rows=62
width=40) (actual time=0.127..1.106 rows=61 loops=1)
Index Cond: ((lac)::text = '600'::text)
Total runtime: 1.642 ms
(3 rows)

Rod Taylor wrote:

>>TRACE:
>>The original tables are much bigger, so I've tried to simplify things here.
>>Please let me know if there is anything that I could help you with.
>>
>>
>
>You can't do that and expect to get reasonable results. The plans will
>change with the volume of data.
>
>Send an explain analyze of the true problem after ANALYZE with seqscan
>on and one with it off.
>
>Thanks.
>
>