Re: POSTGRES DB 3 800 000 rows table, speed up? - Mailing list 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@socialserve.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