Thread: The old "not using index" question

The old "not using index" question

From
"Jan-Philipp 'Thefly' Reining"
Date:
Hi there,

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

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

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



regards,
Jan-Philipp


Re: The old "not using index" question

From
Hubert depesz Lubaczewski
Date:
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


Re: The old "not using index" question

From
"Jan-Philipp 'Thefly' Reining"
Date:
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:
> > ---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
>
>


Re: The old "not using index" question

From
Hubert depesz Lubaczewski
Date:
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


Re: The old "not using index" question

From
"Jan-Philipp 'Thefly' Reining"
Date:
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:
> > > 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
>
>


Re: The old "not using index" question

From
Tom Lane
Date:
"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

Re: The old "not using index" question

From
Stephan Szabo
Date:
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?