Re: POSTGRES DB 3 800 000 rows table, speed up?

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
<jlrobins(at)socialserve(dot)com> 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