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

reina(at)nsi(dot)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