THanks for quick reply
this is what i get now
Index Scan using ipt on ipdb2 (cost=0.00..74265.76 rows=989636
width=118) (actual time=0.216..2379.608 rows=1 loops=1)
Index Cond: (3229285376::bigint <= ipto)
Filter: (3229285376::bigint >= ipfrom)
Total runtime: 2379.666 ms
(4 rows)
detectlo_db=> EXPLAIN ANALYZE SELECT * FROM ipdb2 WHERE '999998376'
BETWEEN ipfrom AND ipto;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using ipf on ipdb2 (cost=0.00..6796.64 rows=154129 width=118)
(actual time=211.298..211.301 rows=1 loops=1)
Index Cond: (999998376::bigint >= ipfrom)
Filter: (999998376::bigint <= ipto)
Total runtime: 211.371 ms
(4 rows)
detectlo_db=> drop index ipt
detectlo_db-> ;
DROP INDEX
detectlo_db=> analyze ipdb2;
ANALYZE
detectlo_db=> EXPLAIN SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN
ipfrom AND ipto;
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using ipf on ipdb2 (cost=0.00..95179.56 rows=989181 width=117)
Index Cond: (3229285376::bigint >= ipfrom)
Filter: (3229285376::bigint <= ipto)
(3 rows)
detectlo_db=> EXPLAIN ANALYZE SELECT * FROM ipdb2 WHERE '3229285376'
BETWEEN ipfrom AND ipto;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using ipf on ipdb2 (cost=0.00..95179.56 rows=989181
width=117) (actual time=3223.344..3223.347 rows=1 loops=1)
Index Cond: (3229285376::bigint >= ipfrom)
Filter: (3229285376::bigint <= ipto)
Total runtime: 3223.410 ms
(4 rows)
detectlo_db=> SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom AND
ipto;
ipfrom | ipto | countryshort | countrylong | ipregion |
ipcity | iplatitude | iplongitude | ipzipcode |
ipisp | ipdomain
------------+------------+--------------+---------------+----------+--------+------------+-------------+-----------+-----------------------------------+----------
3229285376 | 3229285631 | US | UNITED STATES | TEXAS |
TYLER | 32.3511 | -95.2922 | 75701 | HOWE-BAKER ENGINEERS
INCORPORATED | -
(1 row)
detectlo_db=>
On Wed, 28 Dec 2005 23:33:41 +0200, James Robinson
<[EMAIL PROTECTED]> wrote:
On Dec 28, 2005, at 4:24 PM, Eugene wrote:
THIS is what I get
[snip ]
Sorry -- use 'explain select ...', not 'analyze select ...' my bad.
But anyway, looks like your query is still being served by your
preexisting 'ipt' index. Try
drop index ipt;
then
analyze ipdb2
then
explain analyze select * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom
AND ipto;
[ Assuming this is not a currently running produciotn server ]
----
James Robinson
Socialserve.com
--
----------------
eugene
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
