query not using index

From: Greg Janée <gjanee(at)alexandria(dot)ucsb(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: query not using index
Date: 2007-05-05 05:42:28
Message-ID: D314E63E-E843-4951-A0D0-F5176EB35AC4@alexandria.ucsb.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Kirkwood 2007-05-05 07:19:09 Re: Feature Request --- was: PostgreSQL Performance Tuning
Previous Message brian 2007-05-05 04:31:20 Re: An alternatives to rules and triggers