From: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | index issues with generate_series.....or why this index os not working |
Date: | 2011-06-07 18:26:32 |
Message-ID: | BANLkTinQna6+0i-dhONQ6zYq=4ym8RZMDw@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greetings!!
I'm not sure why the query is not using the gist index in the table
base.parishes. Any suggestions?
______________________________________________
CREATE TABLE base.parishes
(
gid serial NOT NULL,
parish text,
"COUNT" integer,
"SUM_AREA" double precision,
"SUM_ELECTO" double precision,
the_geom geometry,
CONSTRAINT parishes_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3448)
)
WITH (
OIDS=FALSE
);
ALTER TABLE base.parishes OWNER TO postgres;
-- Index: base.gfd7ir67uftyujrth7ji68t678jttyrdgd
-- DROP INDEX base.gfd7ir67uftyujrth7ji68t678jttyrdgd;
CREATE INDEX gfd7ir67uftyujrth7ji68t678jttyrdgd
ON base.parishes
USING gist
(the_geom);
______________________________________________
The query:
______________________________________________
with mnme as (
select generate_series(600000,840000,2) mn, generate_series(610000,710000,2)me
),
points as
(select st_makepoint(mn,me), parish from mnme inner join
base.parishes on
st_intersects(setsrid(st_makepoint(mn,me),3448),the_geom))
select * from points
______________________________________________
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2011-06-07 18:38:09 | Re: maximum size limit for a query string? |
Previous Message | Owen Marinas | 2011-06-07 18:05:21 | replication problems 9.0 |