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

On Sat, Jun 14, 2003 at 08:27:20AM -0500, Bruno Wolff III wrote:
> 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'

It would be useful if we didn't have to immediately consider MVCC info,
which requires hitting the tupples. Indexes are usually much narrower
than tables, even with metadata included, so you can scan two indexes
and and/or the results faster than scanning one index, then hitting all
the tuples.

I believe this could be done right now in pgsql, though I'm not sure if
it would be as useful since you'll still have to read all the tupples to
obtain the MVCC info (this is why I hope the MVCC info can eventually be
moved out of mainline storage).

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

What indexes are on crate?
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"