query not using index
От | Greg Janée |
---|---|
Тема | query not using index |
Дата | |
Msg-id | D314E63E-E843-4951-A0D0-F5176EB35AC4@alexandria.ucsb.edu обсуждение исходный текст |
Ответы |
Re: query not using index
|
Список | 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
В списке pgsql-general по дате отправления: