Thread: explain analyze functionality

i have an SQL query of the general type

select att0 from table where 1 <= att0 and att0 < 1000000

to a table of the general format (att0 int4, att1 int4) where there is an
index on att0 and when i run the explain analyze on that question it
returns me the following result:

NOTICE:  QUERY PLAN:

Index Scan using index1 on class y  (cost=0.00..3662.16 rows=1266 width=4)
(actual time=0.44..4323.38 rows=1000000 loops=1)
Total runtime: 4563.40 msec

NOTICE:  QUERY PLAN:

Index Scan using index1 on class y  (cost=0.00..3662.16 rows=1266 width=4)
(actual time=0.44..4323.38 rows=1000000 loops=1)
Total runtime: 4563.40 msec

doews anyone know each of those two index scans, which are stated there
are unique to which of the two conditions refer: to condition 1 <= att0,
to condition att0 < 1000000 or to the union of those two questions? I
would appreciate any opinion, help.




                                                 SWTHRHS TOYRTOYNHS
                                                (tourtoun@csd.uch.gr)

Tourtounis Sotiris <tourtoun@csd.uoc.gr> writes:
> i have an SQL query of the general type
> select att0 from table where 1 <= att0 and att0 < 1000000
> to a table of the general format (att0 int4, att1 int4) where there is an
> index on att0 and when i run the explain analyze on that question it
> returns me the following result:

> NOTICE:  QUERY PLAN:

> Index Scan using index1 on class y  (cost=0.00..3662.16 rows=1266 width=4)
> (actual time=0.44..4323.38 rows=1000000 loops=1)
> Total runtime: 4563.40 msec

> doews anyone know each of those two index scans, which are stated there
> are unique to which of the two conditions refer: to condition 1 <= att0,
> to condition att0 < 1000000 or to the union of those two questions?

It should be using both.  As of 7.3 it's possible to tell this from the
EXPLAIN output:

regression=# explain select * from tenk1 where 1 < unique1 and unique1 < 100;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..353.69 rows=100 width=244)
   Index Cond: ((1 < unique1) AND (unique1 < 100))
(2 rows)

but in older releases you have to grovel through EXPLAIN VERBOSE output
if you want to be sure.

            regards, tom lane