Re: Odd performance / query plan with bitmasked field as opposed to equality

On Mon, Jul 13, 2009 at 4:46 PM, Frank Joerdens<frank(at)joerdens(dot)de> wrote:
> I can't figure what is going on below; first of all, this count  which
> returns 1.5 million from a ~2 million row table:
>
> woome=# explain analyze SELECT COUNT(*) FROM "webapp_person" WHERE
> "webapp_person"."permissionflags" =
> B'0000000000001111111111111111111111111111'::"bit";
>                                                           QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=125774.83..125774.84 rows=1 width=0) (actual
> time=2976.405..2976.405 rows=1 loops=1)
>   ->  Seq Scan on webapp_person  (cost=0.00..122041.10 rows=1493490
> width=0) (actual time=0.019..2781.735 rows=1518635 loops=1)
>         Filter: (permissionflags =
> B'0000000000001111111111111111111111111111'::"bit")
>  Total runtime: 2976.475 ms
> (4 rows)

There are two possibilities here: the planner thinks it CAN'T use the
relevant index for this query, or it thinks that the index will be
slower than just seq-scaning the whole table. To figure out which it
is, try EXPLAIN ANALYZE again with enable_seqscan set to false (note:
this is a bad idea in general, but useful for debugging). If you
still get a seqscan anyway, then there's some reason why it thinks
that it can't use the index (which we can investigate). If that makes
it switch to an index scan, then you can try adjusting your cost
parameters. But the first thing is to figure out which kind of
problem you have. In any case, send the output to the list.

Solving this problem will probably shed some light on the other things
in your original email, so I'm not going to specifically address each
one at this point.

...Robert