Обсуждение: query not using index

Hi, Postgres is refusing to use a GIST index on a spatial column.
Here's the table and column and index:

                    Table "public.scene"
        Column        |          Type           | Modifiers
---------------------+-------------------------+-----------
  ...
  footprint           | geometry                | not null
Indexes:
     ...
     "idxscenefootprint" gist (footprint)

Index "public.idxscenefootprint"
   Column   | Type
-----------+-------
  footprint | box2d
gist, for table "public.scene"


This table has about 8,000,000 rows.  Note in the following that even
when I disable sequential scans, it still does a sequential scan!


db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
'((-120.1, 34.3), (-119.7, 34.4))' ;
                                                     QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------
Seq Scan on scene a  (cost=0.00..369700.89 rows=42196 width=252)
(actual time=50.064..47748.609 rows=507 loops=1)
    Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box)
Total runtime: 47749.094 ms
(3 rows)

db=> set enable_seqscan = off;
SET
db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
'((-120.1, 34.3), (-119.7, 34.4))' ;
                                                           QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
Seq Scan on scene a  (cost=100000000.00..100369700.89 rows=42196
width=252) (actual time=47.405..48250.899 rows=507 loops=1)
    Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box)
Total runtime: 48251.422 ms
(3 rows)


Also, when I look at pg_stats, there's no histogram for the footprint
column (and this is right after I did an analyze):


db=> select * from pg_stats where tablename='scene' and
attname='footprint';
schemaname | tablename |  attname  | null_frac | avg_width |
n_distinct | most_common_vals | most_common_freqs | histogram_bounds
| correlation
------------+-----------+-----------+-----------+-----------
+------------+------------------+-------------------
+------------------+-------------
public     | scene     | footprint |         0 |       109 |
-1 |                  |                   |                  |
(1 row)


It's as though the index didn't even exist.

I'm using PostgreSQL 8.0.3 and PostGIS 1.0.0.

Thanks,
-Greg

=?ISO-8859-1?Q?Greg_Jan=E9e?= <gjanee@alexandria.ucsb.edu> writes:
> db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
> '((-120.1, 34.3), (-119.7, 34.4))' ;
>                                                      QUERY PLAN
> ------------------------------------------------------------------------
> -------------------------------------------
> Seq Scan on scene a  (cost=0.00..369700.89 rows=42196 width=252)
> (actual time=50.064..47748.609 rows=507 loops=1)
>     Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Total runtime: 47749.094 ms
> (3 rows)

This appears to be using the "box && box" operator.  I'm not sure which
operators a GIST geometry index supports, but evidently that's not one
of them.  You probably want to cast the other operand differently.
How, I dunno --- the postgis lists would be a better place to ask
than here.

            regards, tom lane

Thanks, that was the problem: postgres thought it had to typecast the
column to a box type, which prevented use of the index.

For any PostGIS users reading this: the solution is to express the
other operand using a GeometryFromText(...) construct.

Thanks again,
-Greg

On May 5, 2007, at 7:48 AM, Tom Lane wrote:

> =?ISO-8859-1?Q?Greg_Jan=E9e?= <gjanee@alexandria.ucsb.edu> writes:
>> db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
>> '((-120.1, 34.3), (-119.7, 34.4))' ;
>>                                                      QUERY PLAN
>> ---------------------------------------------------------------------
>> ---
>> -------------------------------------------
>> Seq Scan on scene a  (cost=0.00..369700.89 rows=42196 width=252)
>> (actual time=50.064..47748.609 rows=507 loops=1)
>>     Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box)
>                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> Total runtime: 47749.094 ms
>> (3 rows)
>
> This appears to be using the "box && box" operator.  I'm not sure
> which
> operators a GIST geometry index supports, but evidently that's not one
> of them.  You probably want to cast the other operand differently.
> How, I dunno --- the postgis lists would be a better place to ask
> than here.
>
>             regards, tom lane