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