Re: why sequential scan is used on indexed column ???

On Sat, Jun 14, 2008 at 04:59:44PM +0200, Andreas Kretschmer wrote:
> Julius Tuskenis <julius(dot)tuskenis(at)gmail(dot)com> schrieb:
> > I have a question concerning performance. One of my queries take a long
> > to execute. I tried to do "explain analyse" and I see that the
> > sequential scan is being used, although I have indexes set on columns
> > that are used in joins. The question is - WHY, and how to change that
> > behavior???
>
> Try to create an index on apsilankymai.sas_id

In the DDL that Julius posted apsilankymai doesn't have an sas_id
column.

The join is on apsilankymai.aps_saskaita = b_saskaita.sas_id. Both
columns have an index: b_saskaita.sas_id is a primary key so it
should have an index implicitly, and apsilankymai.aps_saskaita has
an explicit CREATE INDEX statement. The WHERE clause is on
b_saskaita.sas_subjektas, which also has an explicit CREATE INDEX
statement. Unless I'm mistaken all relevant columns have an index.

A few of the row count estimates differ from reality:

> Hash Join (cost=5.17..10185.89 rows=6047 width=138) (actual time=10698.539..10698.539 rows=0 loops=1)

> Bitmap Heap Scan on b_saskaita (cost=2.03..5.14 rows=9 width=96) (actual time=31.473..31.489 rows=1 loops=1)

However, that might not be entirely responsible for the questionable
plan. I created a test case that has close to the same estimated and
actual row counts and has the same plan if I disable enable_nestloop:

set enable_nestloop to off;

explain analyze
select *
FROM b_saskaita JOIN apsilankymai ON (aps_saskaita = sas_id)
where sas_subjektas = 20190;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=6.54..5814.42 rows=5406 width=286) (actual time=3222.429..3222.429 rows=0 loops=1)
Hash Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id)
-> Seq Scan on apsilankymai (cost=0.00..4627.50 rows=300350 width=42) (actual time=0.085..1514.863 rows=300350 loops=1)
-> Hash (cost=6.43..6.43 rows=9 width=244) (actual time=0.122..0.122 rows=1 loops=1)
-> Bitmap Heap Scan on b_saskaita (cost=2.32..6.43 rows=9 width=244) (actual time=0.089..0.095 rows=1 loops=1)
Recheck Cond: (sas_subjektas = 20190)
-> Bitmap Index Scan on fki_sas_subjektas (cost=0.00..2.32 rows=9 width=0) (actual time=0.066..0.066 rows=1 loops=1)
Index Cond: (sas_subjektas = 20190)
Total runtime: 3222.786 ms

I get a better plan if I enable nested loops:

set enable_nestloop to on;

explain analyze
select *
FROM b_saskaita JOIN apsilankymai ON (aps_saskaita = sas_id)
where sas_subjektas = 20190;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=79.93..4660.23 rows=5406 width=286) (actual time=1.000..1.000 rows=0 loops=1)
-> Seq Scan on b_saskaita (cost=0.00..10.25 rows=9 width=244) (actual time=0.116..0.870 rows=1 loops=1)
Filter: (sas_subjektas = 20190)
-> Bitmap Heap Scan on apsilankymai (cost=79.93..441.58 rows=6007 width=42) (actual time=0.084..0.084 rows=0 loops=1)
Recheck Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id)
-> Bitmap Index Scan on idx_aps_saskaita (cost=0.00..78.43 rows=6007 width=0) (actual time=0.068..0.068 rows=0 loops=1)
Index Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id)
Total runtime: 1.321 ms

Julius, do you perchance have enable_nestloop = off? If so, do you
get a better plan if you enable it? Also, have you run ANALYZE
lately?

--
Michael Fuhr