Re: Index not being used in MAX function (7.2.3) - Mailing list pgsql-general

On Fri, Jun 13, 2003 at 17:36:49 -0500,
  "Jim C. Nasby" <jim@nasby.net> wrote:
>
> I believe MSSQL and Oracle support it; they scan the indexes then grab
> the appropriate set of matching tuple addresses. The advantage you get
> from this is you can get close to multi-key index performance without
> using multi-key indexes. This is useful when you need to do lookups on
> two different fields in a table, and also need to do lookups on both
> fields. IE:
>
> select ... where a=foo;
> select ... where b=bar;
> select ... where a=blah and b=baz;
>
> I was strictly looking to the future when pgsql migth eventually support
> this. :)

I don't see how this would be useful in any of these examples. The first
two seem to be ones where one index scan would work. The third would
be handled by postgres using an index scan and a filter (assuming no
multikey index was available). I can't think of a circumtances where
doing two index scans and then joining the rows obtained from each
scan would be faster than the way postgres does it. If there was an 'or'
instead of an 'and' then unioning the two sets of results from index
scans would make sense. I tried a quick test of this and saw postgres
using a seq scan with a filter, but I might not have data with the
right set of properties to make this work. In theory you could do
a plain index scan for one half of the or and an index scan with a filter
(to remove what would be duplicates) on the other half of the or clause.
This would be a win in many common cases. I tried some stuff to see
what postgres does and it looks like it has a way to search two indexes
at once.

The following commands:

select version();

explain analyze
  select gameid from crate where areaid = 'JL005' or rate = 5034;

explain analyze
  (select gameid from crate where areaid = 'JL005') union all
  (select gameid from crate where rate = 5034 and areaid <> 'JL005');

generated the following output:

                                version
------------------------------------------------------------------------
 PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

                                                         QUERY PLAN
    

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using crate_pkey, "temp" on crate  (cost=0.00..151.34 rows=38 width=7) (actual time=0.09..0.72 rows=72
loops=1)
   Index Cond: ((areaid = 'JL005'::text) OR (rate = 5034))
 Total runtime: 0.85 msec
(3 rows)

                                                          QUERY PLAN
      

------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..151.32 rows=39 width=7) (actual time=0.08..0.73 rows=72 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..39.88 rows=10 width=7) (actual time=0.07..0.10 rows=2 loops=1)
         ->  Index Scan using crate_pkey on crate  (cost=0.00..39.88 rows=10 width=7) (actual time=0.07..0.09 rows=2
loops=1)
               Index Cond: (areaid = 'JL005'::text)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..111.43 rows=29 width=7) (actual time=0.03..0.57 rows=70 loops=1)
         ->  Index Scan using "temp" on crate  (cost=0.00..111.43 rows=29 width=7) (actual time=0.03..0.44 rows=70
loops=1)
               Index Cond: (rate = 5034)
               Filter: (areaid <> 'JL005'::text)
 Total runtime: 0.96 msec
(9 rows)

The select with the union was an attempt to force the results of two index
scans to be combined. But if you look at the results of teh plan for the
simpler query you will see that postgres is doing an index scan with
an 'or' condition which suggests that it is doing pretty much the same thing
as the more complicated query, but more efficiently.

P.S.
In my example yesterday there were suppposed to be limit 1 clauses
in both subselects as the were supposed to be the equivalent of min
functions.