Re: Planner very slow on same query to slightly

If I understand correctly, I tried specifying the target and even casting
all of the smallint's, but it still is a slow estimate. Perhaps, this is
just due to a large amount of data, but my gut is telling me that I have
something wrong here.

db02=# explain select distinct area from center_out_cell where subject =
'M' and arm = 'R' and rep = 10 and success = 1::smallint and direction =
1::smallint and target = 3::smallint;
NOTICE: QUERY PLAN:
Unique (cost=100105115.88..100105115.93 rows=2 width=5)
-> Sort (cost=100105115.88..100105115.88 rows=19 width=5)
-> Seq Scan on center_out_cell (cost=100000000.00..100105115.47
rows=19 width=5)
EXPLAIN
db02=# explain select distinct area from center_out_cell where subject =
'M' and arm = 'R' and rep = 10::int and success = 1::smallint and direction
= 1::smallint and target = 3::smallint;
NOTICE: QUERY PLAN:
Unique (cost=100105115.88..100105115.93 rows=2 width=5)
-> Sort (cost=100105115.88..100105115.88 rows=19 width=5)
-> Seq Scan on center_out_cell (cost=100000000.00..100105115.47
rows=19 width=5)
EXPLAIN
db02=#

-Tony

At 09:47 PM 7/17/02 -0400, Tom Lane wrote:
>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