Forum OpenACS Development: Response to Replacement for places package.

Collapse
Posted by Domingo Alvarez duarte on
A small test I did to see what could happen:

-------
Database created using "select dad_populate_areas(8);"
it took 80 minutes on a machine "Duron 800MHZ 256M"

Now some play

++++++ start ++++++ vie may 25 00:44:46 CEST 2001
psql -c "explain select count(*) from dad_areas;" dad_test
NOTICE:  QUERY PLAN:

Aggregate  (cost=57195.47..57195.47 rows=1 width=0)
  ->  Seq Scan on dad_areas  (cost=0.00..51203.58 rows=2396758 width=0)

EXPLAIN
psql -c "select count(*) from dad_areas;" dad_test
  count  
---------
 2396758
(1 row)

------ end -------- vie may 25 00:44:46 CEST 2001 - vie may 25 00:45:24 CEST 2001
++++++ start ++++++ vie may 25 00:45:24 CEST 2001
psql -c "explain select * from dad_areas where area_id=1034876;" dad_test
NOTICE:  QUERY PLAN:

Index Scan using dad_areas_pkey on dad_areas  (cost=0.00..5.01 rows=1 width=165)

EXPLAIN
psql -c "select * from dad_areas where area_id=1034876;" dad_test
 area_id | level_1 | level_2 | level_3 | level_4 | level_5 | level_6 | level_7 | area_type_id |   name    | short_name | zip | translation_id 
---------+---------+---------+---------+---------+---------+---------+---------+--------------+-----------+------------+-----+----------------
 1034876 |       7 |       4 |       6 |       1 |       5 |       4 |       7 |            7 | level_7-7 | es         |     |               
(1 row)

------ end -------- vie may 25 00:45:24 CEST 2001 - vie may 25 00:45:25 CEST 2001
++++++ start ++++++ vie may 25 00:45:25 CEST 2001
psql -c "explain update dad_areas set short_name='es' where area_id between 1345000 and 1500000;" dad_test
NOTICE:  QUERY PLAN:

Index Scan using dad_areas_pkey on dad_areas  (cost=0.00..46492.07 rows=11984 width=157)

EXPLAIN
psql -c "update dad_areas set short_name='es' where area_id between 1345000 and 1500000;" dad_test
UPDATE 155001
------ end -------- vie may 25 00:45:25 CEST 2001 - vie may 25 00:46:17 CEST 2001
++++++ start ++++++ vie may 25 00:46:17 CEST 2001
psql -c "explain select * from dad_area_tree where seed=1879000;" dad_test
NOTICE:  QUERY PLAN:

Subquery Scan dad_area_tree  (cost=0.00..27779.22 rows=7 width=193)
  ->  Limit  (cost=0.00..27779.22 rows=7 width=193)
        ->  Nested Loop  (cost=0.00..242949.23 rows=61 width=193)
              ->  Index Scan using dad_areas_pkey on dad_areas a2  (cost=0.00..5.01 rows=1 width=28)
              ->  Seq Scan on dad_areas a1  (cost=0.00..51203.58 rows=2396758 width=165)

EXPLAIN
psql -c "select * from dad_area_tree where seed=1879000;" dad_test
  seed   | area_id | level_1 | level_2 | level_3 | level_4 | level_5 | level_6 | level_7 | area_type_id |   name    | short_name | zip | translation_id 
---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------+-----------+------------+-----+----------------
 1879000 | 1797572 |      10 |       0 |       0 |       0 |       0 |       0 |       0 |            1 | level_1-7 |            |     |               
 1879000 | 1872471 |      10 |       3 |       0 |       0 |       0 |       0 |       0 |            2 | level_2-3 |            |     |               
 1879000 | 1877153 |      10 |       3 |       2 |       0 |       0 |       0 |       0 |            3 | level_3-2 |            |     |               
 1879000 | 1878909 |      10 |       3 |       2 |       4 |       0 |       0 |       0 |            4 | level_4-4 |            |     |               
 1879000 | 1878983 |      10 |       3 |       2 |       4 |       2 |       0 |       0 |            5 | level_5-2 |            |     |               
 1879000 | 1878993 |      10 |       3 |       2 |       4 |       2 |       2 |       0 |            6 | level_6-2 |            |     |               
 1879000 | 1879000 |      10 |       3 |       2 |       4 |       2 |       2 |       7 |            7 | level_7-7 |            |     |               
(7 rows)

------ end -------- vie may 25 00:46:17 CEST 2001 - vie may 25 00:46:56 CEST 2001
++++++ start ++++++ vie may 25 00:46:56 CEST 2001
psql -c "explain select count(*) from dad_areas where level_1=5 and level_2=2 and level_3=7;" dad_test
NOTICE:  QUERY PLAN:

Aggregate  (cost=5.02..5.02 rows=1 width=0)
  ->  Index Scan using dad_unique_area on dad_areas  (cost=0.00..5.02 rows=1 width=0)

EXPLAIN
psql -c "select count(*) from dad_areas where level_1=5 and level_2=2 and level_3=7;" dad_test
 count 
-------
  4681
(1 row)

------ end -------- vie may 25 00:46:56 CEST 2001 - vie may 25 00:46:57 CEST 2001
++++++ start ++++++ vie may 25 00:46:57 CEST 2001
psql -c "explain update dad_areas set name= name || short_name where short_name='es';" dad_test
NOTICE:  QUERY PLAN:

Seq Scan on dad_areas  (cost=0.00..57195.47 rows=11984 width=171)

EXPLAIN
psql -c "update dad_areas set name= name || short_name where short_name='es';" dad_test
UPDATE 355002
------ end -------- vie may 25 00:46:57 CEST 2001 - vie may 25 00:50:36 CEST 2001
++++++ start ++++++ vie may 25 00:50:36 CEST 2001
psql -c "explain select count(*) from dad_areas where short_name='es';" dad_test
NOTICE:  QUERY PLAN:

Aggregate  (cost=57225.43..57225.43 rows=1 width=0)
  ->  Seq Scan on dad_areas  (cost=0.00..57195.47 rows=11984 width=0)

EXPLAIN
psql -c "select count(*) from dad_areas where short_name='es';" dad_test
 count  
--------
 355002
(1 row)

------ end -------- vie may 25 00:50:36 CEST 2001 - vie may 25 00:51:09 CEST 2001
-------