Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux