Forum OpenACS Development: Response to Replacement for places package.
Posted by
Domingo Alvarez duarte
on 05/25/01 12:40 AM
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
-------
