Re: Planner very slow on same query to slightly - Mailing list pgsql-hackers

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@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