The old "not using index" question

On Fri, Nov 29, 2002 at 05:37:04PM +0100, Jan-Philipp 'Thefly' Reining wrote:

---but this one does not?
SELECT *
FROM ttm_slots s
WHERE s.peering = 72
AND s.ranking < 1050
AND s.ranking > 950

The index ist "btree", so it should be able to use the index with a < >
comparison?

show us explain analyze output.
probably planner thinks there are too many rows matching this criteria
to use index.

depesz

--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
M�j Bo�e, spraw abym milcza�, dop�ki si� nie upewni�, �e naprawd� mam
coďż˝ do powiedzenia. (c) 1998 depesz

Hi there,

show us explain analyze output.

Here we go:

---this one is using the index on "ranking"
SELECT *
FROM ttm_slots s
WHERE s.peering = 72
AND s.ranking = 1050

explain:
Index Scan using ranking_ttm_slots_key on ttm_slots s (cost=0.00..191.06
rows=8 width=62)

---but this one does not?
SELECT *
FROM ttm_slots s
WHERE s.peering = 72
AND s.ranking < 1050
AND s.ranking > 950

explain:
Seq Scan on ttm_slots s (cost=0.00..1823.64 rows=7949 width=62)

The index ist "btree", so it should be able to use the index with a < >
comparison?

\d ranking_ttm_slots_key
Index "ranking_ttm_slots_key"
Column | Type
---------+---------
ranking | integer
btree

regards,

// Jan-Philipp 'Thefly' Reining

----- Original Message -----
From: "Hubert depesz Lubaczewski" <depesz@depesz.pl>
To: "Jan-Philipp 'Thefly' Reining" <jpr@turtle-entertainment.de>;
<pgsql-general@postgresql.org>
Sent: Saturday, November 30, 2002 1:20 AM
Subject: Re: [GENERAL] The old "not using index" question

On Fri, Nov 29, 2002 at 05:37:04PM +0100, Jan-Philipp 'Thefly' Reining

wrote:

On Mon, Dec 02, 2002 at 01:56:30PM +0100, Jan-Philipp 'Thefly' Reining wrote:

show us explain analyze output.

explain:

not explain. explain analyze. this information is much more valuable.

depesz

--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
M�j Bo�e, spraw abym milcza�, dop�ki si� nie upewni�, �e naprawd� mam
coďż˝ do powiedzenia. (c) 1998 depesz

EXPLAIN
=> explain analyze SELECT *
-> FROM ttm_slots s
-> WHERE s.peering = 72
-> AND s.ranking = 1050
-> ;
NOTICE: QUERY PLAN:

Index Scan using ranking_ttm_slots_key on ttm_slots s (cost=0.00..191.06
rows=8 width=62) (actual time=0.26..0.83 rows=7 loops=1)
Total runtime: 1.02 msec

EXPLAIN
=> explain analyze SELECT *
-> FROM ttm_slots s
-> WHERE s.peering = 72
-> AND s.ranking < 1050
-> AND s.ranking > 950
-> ;
NOTICE: QUERY PLAN:

Seq Scan on ttm_slots s (cost=0.00..1823.64 rows=7949 width=62) (actual
time=0.08..361.12 rows=9840 loops=1)
Total runtime: 379.47 msec

// Jan-Philipp 'Thefly' Reining

----- Original Message -----
From: "Hubert depesz Lubaczewski" <depesz@depesz.pl>
To: "Jan-Philipp 'Thefly' Reining" <jpr@turtle-entertainment.de>;
<pgsql-general@postgresql.org>
Sent: Monday, December 02, 2002 2:07 PM
Subject: Re: [GENERAL] The old "not using index" question

On Mon, Dec 02, 2002 at 01:56:30PM +0100, Jan-Philipp 'Thefly' Reining

wrote:

"Jan-Philipp 'Thefly' Reining" <jpr@turtle-entertainment.de> writes:

Index Scan using ranking_ttm_slots_key on ttm_slots s (cost=0.00..191.06
rows=8 width=62) (actual time=0.26..0.83 rows=7 loops=1)
Total runtime: 1.02 msec

Seq Scan on ttm_slots s (cost=0.00..1823.64 rows=7949 width=62) (actual
time=0.08..361.12 rows=9840 loops=1)
Total runtime: 379.47 msec

I kinda think the planner made the right choice here. Assuming that it
will take 1400 times longer to select 1400 times more rows via
indexscan, the indexed plan for the second query would take something
upwards of 1100 msec.

You could check it by "set enable_seqscan = off" and then repeat the
second EXPLAIN ANALYZE.

You will also find that as you narrow the range of the range query,
the planner will eventually prefer an indexscan. Ideally it will
switch over somewhere around the point where the runtimes are actually
equal ;-) ... but I'd be ecstatic if it gets it right within a factor of
2.

Beware of disk cache effects when doing this sort of test --- repeating
the identical query often gives a lower actual runtime on second and
subsequent tests.

regards, tom lane

On Mon, 2 Dec 2002, Jan-Philipp 'Thefly' Reining wrote:

EXPLAIN
=> explain analyze SELECT *
-> FROM ttm_slots s
-> WHERE s.peering = 72
-> AND s.ranking = 1050
-> ;
NOTICE: QUERY PLAN:

Index Scan using ranking_ttm_slots_key on ttm_slots s (cost=0.00..191.06
rows=8 width=62) (actual time=0.26..0.83 rows=7 loops=1)
Total runtime: 1.02 msec

EXPLAIN
=> explain analyze SELECT *
-> FROM ttm_slots s
-> WHERE s.peering = 72
-> AND s.ranking < 1050
-> AND s.ranking > 950
-> ;
NOTICE: QUERY PLAN:

Seq Scan on ttm_slots s (cost=0.00..1823.64 rows=7949 width=62) (actual
time=0.08..361.12 rows=9840 loops=1)
Total runtime: 379.47 msec

Have you tried running these after a
set enable_seqscan=off;

The row estimates seem to be reasonably correct (comparing real rows to
estimated rows) so I'd guess that it's estimating that it's returning
enough of the rows to make the sequence scan faster. How many rows are in
ttm_slots?