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

Hi Michael.

Thank you for your answer. I've checked - enable_nestloop is true. I did
ANALYZE, but that didn't help. The sequential scan is still used.... Any
more ideas why?

Julius Tuskenis

Michael Fuhr rašė:
> 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?
>
>