SELECT that uses sequential scan instead of index scan

Question

I'm trying to optimize some of my selects using the explain analyze, and I can't understand why postgresql uses a sequentials scan instead of index scan:

explain analyze SELECT SUM(a.deure)-SUM(a.haver) as Value FROM assentaments a
LEFT JOIN comptes c ON a.compte_id = c.id WHERE c.empresa_id=2 AND c.nivell=11 AND
(a.data >='2007-01-01' AND a.data <='2007-01-31')  AND c.codi_compte LIKE '6%';


------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=44250.26..44250.27 rows=1 width=12)
(actual time=334.054..334.054 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..44249.20 rows=211 width=12)
      (actual time=65.277..333.179 rows=713 loops=1)
    ->  Seq Scan on comptes c  (cost=0.00..8001.72 rows=118 width=4)
        (actual time=0.053..64.287 rows=236 loops=1)
        Filter: (((codi_compte)::text ~~ '6%'::text) AND
        (empresa_id = 2) AND (nivell = 11))
      ->  Index Scan using index_compte_id on assentaments a
          (cost=0.00..307.16 rows=2 width=16) (actual time=0.457..1.138 rows=3 loops=236)
           Index Cond: (a.compte_id = c.id)
           Filter: ((a.data >= '2007-01-01'::date) AND (a.data <= '2007-01-31'::date))

  Total runtime: 334.104 ms
  (8 rows)

I've created a custom index:

CREATE INDEX "index_multiple" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST,
empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);

And also I've created three new index for this three fields on comptes table just to check If it takes an index scan, but not, the result is the same:

CREATE INDEX "index_codi_compte" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);
CREATE INDEX "index_comptes" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);
CREATE INDEX "index_multiple" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST,     empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);
CREATE INDEX "index_nivell" ON "public"."comptes" USING btree(nivell ASC NULLS LAST);

thanks!

m.

EDIT:

assentaments.id and assentaments.data have their index also

select count(*) FROM comptes => 148498
select count(*) from assentaments => 2128771

select count(distinct(codi_compte)) FROM comptes => 137008
select count(distinct(codi_compte)) FROM comptes WHERE codi_compte LIKE '6%' => 368
select count(distinct(codi_compte)) FROM comptes WHERE codi_compte LIKE '6%' AND empresa_id=2; => 303

An index on assentaments.id or on assentaments.compte_id? — MatBailie
– MatBailie, Commented Jun 3, 2011 at 13:17
And what happens when you run with set enable_seqscan to off ? Is it faster or slower? — veljkoz
– veljkoz, Commented Jun 3, 2011 at 13:25
@Dems Index on assentaments.compte_id (assentaments.id is the PK) — mongeta
– mongeta, Commented Jun 3, 2011 at 13:49
@veljkoz the same, sequential scan made. The option to off seems correctly applied, but still using seq. (psql (8.4.1)) — mongeta
– mongeta, Commented Jun 3, 2011 at 13:50

bobflux · Accepted Answer · 2011-06-04 08:36:40Z

If you want an index on TEXT to index LIKE queries, you need to create it with text_pattern_ops, like this :

test=> CREATE TABLE t AS SELECT n::TEXT FROM generate_series( 1,100000 ) n;
test=> CREATE INDEX tn ON t(n);
test=> VACUUM ANALYZE t;
test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n LIKE '123%';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=10 width=5) (actual time=0.027..14.631 rows=111 loops=1)
   Filter: (n ~~ '123%'::text)
 Total runtime: 14.664 ms

test=> CREATE INDEX tn2 ON t(n text_pattern_ops);
CREATE INDEX
Temps : 267,589 ms
test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n LIKE '123%';
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=5.25..244.79 rows=10 width=5) (actual time=0.089..0.121 rows=111 loops=1)
   Filter: (n ~~ '123%'::text)
   ->  Bitmap Index Scan on tn2  (cost=0.00..5.25 rows=99 width=0) (actual time=0.077..0.077 rows=111 loops=1)
         Index Cond: ((n ~>=~ '123'::text) AND (n ~<~ '124'::text))
 Total runtime: 0.158 ms

see details here :

http://www.postgresql.org/docs/9.1/static/indexes-opclass.html

If you do not want to create an additional index, and column is a TEXT, you can replace "compte LIKE '6%'" by "compte >= '6' AND compte < '7'" which is a simple index range condition.

test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n >= '123' AND n < '124';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Index Scan using tn on t  (cost=0.00..126.74 rows=99 width=5) (actual time=0.030..0.127 rows=111 loops=1)
   Index Cond: ((n >= '123'::text) AND (n < '124'::text))
 Total runtime: 0.153 ms

In your case this solution is probably better.

MatBailie · Accepted Answer · 2011-06-03 12:57:01Z

2

It appears that the DBMS is estimating that the JOIN on assentaments will be much more restrictive than filtering comptes, then joining.

Options could be...
1. Put an index on assentaments.compte_id
2. Alter your index on comptes to be include id as the first indexed field.

The first option may allow the execution plan to reverse: Filter comptes, then join to assentaments.

The second option may allow the execution plan to stay the same, but enable the use of the index.

answered Jun 3, 2011 at 12:57

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

7 Comments

mongeta Over a year ago

I've edited the question with some notes: this index existed assentaments.compte_id and added some values

MatBailie Over a year ago

I'd still try updating index_multi to additionally include id as the first field. I'm also assuming the last two tests (giving 368 and 303 rows respectively) use an index scan?

mongeta Over a year ago

same result with the id in the index as the first field. The other two selects (368 and 303) are also performed using sequential scann:

mongeta Over a year ago

explain analyze select count(distinct(codi_compte)) FROM comptes WHERE codi_compte LIKE '6%'; => Aggregate (cost=7259.79..7259.80 rows=1 width=11) (actual time=67.276..67.276 rows=1 loops=1) -> Seq Scan on comptes (cost=0.00..7259.23 rows=223 width=11) (actual time=0.046..57.877 rows=990 loops=1) Filter: ((codi_compte)::text ~~ '6%'::text) Total runtime: 67.311 ms (4 rows)

MatBailie Over a year ago

If the test SELECTS are generating sequential scans, I think PostresSQL has a problem with optimising LIKE. Try using LEFT(x,1) = '6'?

|

veljkoz · Accepted Answer · 2011-06-03 13:03:12Z

0

This is most commonly due to the bad statistics on the index, i.e. if the index is not selective enough (for example, many repeating values), accessing and filtering on index can be even more time consuming than doing seq scan.

Are your values on c.codi_compte selective enough? Maybe you have too many null values?

answered Jun 3, 2011 at 13:03

veljkoz

8,5129 gold badges58 silver badges92 bronze badges

3 Comments

mongeta Over a year ago

yes, the values on c.codi_compte are selective, they are unique in each group of the c.empresa_id

veljkoz Over a year ago

Then I guess @Dems is right - using the index on assentaments seems to be evaluated as more effective - how many rows does it filter out versus the filter on comptes? (without the join)

mongeta Over a year ago

SELECT count(*) FROM assentaments a WHERE a.empresa_id=2 AND (a.data >='2007-01-01' AND a.data <='2007-01-31') => 37893 rows

Sean · Accepted Answer · 2011-06-03 15:09:48Z

There are a few things you could/should do. First:

SELECT SUM(a.deure)-SUM(a.haver) as Value

SUM() will touch every row that matches... no way to INDEX that operation.

FROM assentaments a, comptes c

When debugging queries, I find it easier to use a natural JOIN instead of an explicit JOIN. The query planner is freed up a bit more and often times makes a better choice. That's not the case here, just a general comment, however. Here's where there are likely mismatches between your INDEXes and your query.

WHERE TRUE = TRUE
    AND a.compte_id  = c.id
    AND c.empresa_id = 2
    AND c.nivell     = 11

Of those three queries, you have the following INDEX:

CREATE INDEX "index_multiple" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST, empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);

Break that apart since this isn't a UNIQUE INDEX, you shouldn't see any change in the integrity of your data. The reason I'm suggesting this is because I'd guess that codi_compte has a low cardinality. I'd guess that empresa_id would have a higher cardinality. In general, create your INDEXes from highest cardinality to lowest.

I suspect three INDEXes will do a bitmap join or hash join faster. The crux of the problem is that PostgreSQL (probably correctly) thinks that doing an index_scan is more expensive than doing a seq_scan.

    AND (a.data >='2007-01-01' AND a.data <='2007-01-31')
    AND c.codi_compte LIKE '6%';

An INDEX on a.data could also be helpful because PostgreSQL would likely do an index_scan on the date given depending on the number of rows in the assentaments table.

CREATE INDEX "index_codi_compte" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);
CREATE INDEX "index_comptes"     ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);

I don't know why you have this INDEX twice.

CREATE INDEX "index_multiple" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST,     empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);

As per above, break that INDEX apart.

CREATE INDEX "index_nivell" ON "public"."comptes" USING btree(nivell ASC NULLS LAST);

That INDEX is fine.

Quick tip:

SELECT matching, total, matching / total AS "Want this to be a small number"
FROM
    (SELECT count(*)::FLOAT AS matching FROM tbl WHERE col_id = 1) AS matching,
    (SELECT count(*)::FLOAT AS total FROM tbl) AS total;


 matching rows | total rows | want this to be a small number 
---------------+------------+--------------------------------
             1 |         10 |                            0.1
(1 row)

Where the third column ideally is equal to 1/total.

I'm still debuggin this issue. The problem is reproducible in an easier way, thanks for your help!
Have you tuned your config? Try using pgtune as a base set of tunables.

ypercubeᵀᴹ · Accepted Answer · 2011-06-03 15:38:55Z

0

I would try with

a compound index (data, compte_id) on table assentaments and
a compound index (empresa_id, nivell, codi_compte, id) on table comptes

You should also turn that LEFT JOIN into INNER JOIN. The WHERE conditions you have make them equivalent. Perhaps the query planner is not aware of it.

Another suspicion is the type of field comptes.codi_compte. If it is integer and not char(), then the

WHERE c.codi_compte LIKE '6%'

is translated as:

WHERE CAST(c.codi_compte AS CHAR) LIKE '6%'

which means the index cannot be used. If that's the case, you can convert the field to char type.

edited Jun 3, 2011 at 15:38

answered Jun 3, 2011 at 13:32

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

2 Comments

ypercubeᵀᴹ Over a year ago

@mongeta: Can you try the same query but putting INNER JOIN instead of LEFT JOIN ?

mongeta Over a year ago

same as before, but I think the problem is the same in another simple select, so I'm going to try to solve the other before this. thanks!

Collectives™ on Stack Overflow

SELECT that uses sequential scan instead of index scan

EDIT:

5 Answers 5

Comments

7 Comments

3 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

5 Answers 5

Comments

7 Comments

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related