Обсуждение: The old "not using index" question
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
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: > > ---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 > >
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: > > > 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 > >
"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?