Re: Planner very slow on same query to slightly different tables

reina@nsi.edu (Tony Reina) writes:
> db02=# explain select distinct area from center_out_cell where subject
> = 'M' and arm = 'R' and rep = 10 and success = 1 and direction = 1;
> NOTICE:  QUERY PLAN:

> Unique  (cost=87795.47..87795.80 rows=13 width=5)
>   ->  Sort  (cost=87795.47..87795.47 rows=131 width=5)
>         ->  Seq Scan on center_out_cell  (cost=0.00..87790.87 rows=131
> width=5)

> Index "pk1center_out_cell"
>    Column   |     Type
> ------------+--------------
>  subject    | text
>  arm        | character(1)
>  target     | smallint
>  rep        | integer
>  hemisphere | character(1)
>  area       | text
>  filenumber | integer
>  dsp_chan   | text
>  direction  | smallint
> unique btree
> Index predicate: (success = 1)

I imagine the problem with this index is that there's no constraint for
"target" in the query; so the planner could only use the first two index
columns (subject and arm), which probably isn't very selective.  The
index used in the other query is defined differently:

> db02=# \d pk1circles_cell
>   Index "pk1circles_cell"
>    Column   |     Type
> ------------+--------------
>  subject    | text
>  arm        | character(1)
>  rep        | integer
>  direction  | smallint
>  hemisphere | character(1)
>  area       | text
>  filenumber | integer
>  dsp_chan   | text
> unique btree
> Index predicate: (success = 1)

This allows "rep" to be used in the indexscan too (and if you were to
cast properly, viz "direction = 1::smallint", then that column could be
used as well).
        regards, tom lane