Re: why sequential scan is used on indexed column ??? - Mailing list pgsql-general

On Sat, Jun 14, 2008 at 04:59:44PM +0200, Andreas Kretschmer wrote:
> Julius Tuskenis <julius.tuskenis@gmail.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=1loops=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=0loops=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