Thread: Question about a CIDR based query
Hello, Consider that I have the following table: Create Table tmp( route_id int NOT NULL, route cidr NOT NULL, Data varchar(100) NOT NULL) The table contains ~40,000 routes and I have an index on route. The query I am interested in is: select * from tmp where route >>= some_cidr The index on route is not used and I get a sequential scan. The index is used only for the <<= operator. Any idea how I can make the query run faster? Thanks, George
Georgos, > select * from tmp where route >>= some_cidr Can you post an EXPLAIN ANALYZE for this? And when's the last time you ran ANALYZE on the table? > The index on route is not used and I get a sequential scan. The index is > used only for the <<= operator. Most likely Postgres thinks that the >>= query is returning 60% of your table, which makes indexes useless. -- Josh Berkus Aglio Database Solutions San Francisco
I did a vacuum analyze before I run the following explain
June_03=# explain select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN
----------------------------------------------------------------Seq Scan on tmp (cost=0.00..606.60 rows=14544
width=33) Filter: (route >>= '62.1.1.0/24'::cidr)
(2 rows)
The select returns just one route,
Thanks
josh@agliodbs.com (Josh Berkus) wrote in message news:<200406250900.08312.josh@agliodbs.com>...
> Georgos,
>
> > select * from tmp where route >>= some_cidr
>
> Can you post an EXPLAIN ANALYZE for this? And when's the last time you ran
> ANALYZE on the table?
>
> > The index on route is not used and I get a sequential scan. The index is
> > used only for the <<= operator.
>
> Most likely Postgres thinks that the >>= query is returning 60% of your table,
> which makes indexes useless.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
George, Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > June_03=# explain select * from tmp where route >>='62.1.1.0/24'; > QUERY PLAN > ---------------------------------------------------------------- > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > Filter: (route >>= '62.1.1.0/24'::cidr) -- -Josh BerkusAglio Database SolutionsSan Francisco
George, > Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > > > June_03=# explain select * from tmp where route >>='62.1.1.0/24'; > > QUERY PLAN > > ---------------------------------------------------------------- > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > > Filter: (route >>= '62.1.1.0/24'::cidr) Oh, and also a SELECT VERSION(); would be nice. -- Josh Berkus Aglio Database Solutions San Francisco
Hi Josh, Ok, first the explain analyze .... June_03=# explain analyze select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN -----------------------------------------------------------------------------------------------------------Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) (actual time=3.862..15.366 rows=1 loops=1) Filter: (route >>= '62.1.1.0/24'::cidr)Total runtime: 15.493 ms (3 rows) And the version of postgres June_03=# select version(); version --------------------------------------------------------------------------------------------------------------------PostgreSQL 7.4.1on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Gentoo Linux 3.3.2-r2, propolice) (1 row) Thanks, Georgos josh@agliodbs.com (Josh Berkus) wrote in message news:<200406292049.06283.josh@agliodbs.com>... > George, > > > Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > > > > > June_03=# explain select * from tmp where route >>='62.1.1.0/24'; > > > QUERY PLAN > > > ---------------------------------------------------------------- > > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > > > Filter: (route >>= '62.1.1.0/24'::cidr) > > Oh, and also a SELECT VERSION(); would be nice.