Skip site navigation (1) Skip section navigation (2)

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

From: Eugene <evgenius(at)hot(dot)ee>
To: "James Robinson" <jlrobins(at)socialserve(dot)com>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: POSTGRES DB 3 800 000 rows table, speed up?
Date: 2005-12-28 21:38:01
Message-ID: op.s2ijpnmfx97onz@keskkripokeke.starman.ee (view raw or flat)
Thread:
Lists: pgsql-general
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

pgsql-general by date

Next:From: Tino WildenhainDate: 2005-12-28 22:32:11
Subject: Re: sending mail from Postgres
Previous:From: Tom LaneDate: 2005-12-28 21:27:01
Subject: Re: Queries never returning...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group