Please why I can do best performance for my DB 
Author Message
 Please why I can do best performance for my DB

Hello All!
Please do me a answer on my question?
Why I can do more performance for my database:
In pgsql 7.1.3 in machine with 4 Xeon processor and 4Gb memory I start
a postmaster with param: ""/usr/local/pgsql/bin/postmaster -N 250 -B
6000 -o '-S 81920' -i -D /usr/local/pgsql/data" and do explain query

explain SELECT distinct omain.sortvalue,  omain.object_id AS id FROM
object omain , obj_to_obj oo1, object osub1, obj_types tsub1 WHERE
omain.type_id IN (0,343) AND (tsub1.name like 'movie' AND
osub1.object_id=317224 AND oo1.obj1=omain.object_id AND oo1.obj2 =
osub1.object_id AND tsub1.type_id = osub1.type_id) ORDER BY
omain.sortvalue;

NOTICE:  QUERY PLAN:

Unique  (cost=4342924.49..4452626.88 rows=2194048 width=36)
  ->  Sort  (cost=4342924.49..4342924.49 rows=21940477 width=36)
        ->  Merge Join  (cost=88130.33..92632.38 rows=21940477
width=36)
              ->  Sort  (cost=81103.56..81103.56 rows=353966 width=20)
                    ->  Merge Join  (cost=43821.30..48479.85
rows=353966 width=20)
                          ->  Sort  (cost=3425.47..3425.47 rows=95
width=12)
                                ->  Hash Join  (cost=4.05..3422.34
rows=95 width=12)
                                      ->  Index Scan using object_pkey
on object osub1  (cost=0.00..3293.66 rows=3115 width=8)
                                      ->  Hash  (cost=4.04..4.04
rows=3 width=4)
                                            ->  Index Scan using
name_obj_types_key on obj_types tsub1  (cost=0.00..4.04 rows=3
width=4)
                          ->  Sort  (cost=40395.83..40395.83
rows=372589 width=8)
                                ->  Seq Scan on obj_to_obj oo1
(cost=0.00..5917.89 rows=372589 width=8)
              ->  Sort  (cost=7026.77..7026.77 rows=6198 width=16)
                    ->  Index Scan using type_id_object_key,
type_id_object_key on object omain  (cost=0.00..6636.34 rows=6198
width=16)

EXPLAIN

BUT in slow mashine in pgsql 7.0.1 with 2 P800 processor and 2 Gb
memory I start postgress with param "/usr/local/pgsql/bin/postmaster
-N 110 -B 4000 -i -D /usr/oursite/data"

And in the same query I SEE:
Unique  (cost=11462.05..11462.06 rows=0 width=36)
  ->  Sort  (cost=11462.05..11462.05 rows=1 width=36)
        ->  Nested Loop  (cost=0.00..11462.04 rows=1 width=36)
              ->  Index Scan using name_obj_types_key on obj_types
tsub1  (cost=0.00..2.01 rows=1 width=4)
              ->  Materialize  (cost=11460.02..11460.02 rows=1
width=32)
                    ->  Nested Loop  (cost=0.00..11460.02 rows=1
width=32)
                          ->  Nested Loop  (cost=0.00..11454.28 rows=1
width=16)
                                ->  Index Scan using object_pkey on
object osub1  (cost=0.00..4.64 rows=1 width=8)
                                ->  Seq Scan on obj_to_obj oo1
(cost=0.00..6407.06 rows=403406 width=8)
                          ->  Index Scan using object_pkey on object
omain  (cost=0.00..4.65 rows=1 width=16)

EXPLAIN

Please can you show where I do fault?

Thenk's All.
Juri Potapov



Fri, 24 Sep 2004 22:41:38 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Anyone know of some canned (cheap or free) DB performance testing software

2. canned code to get db on web quickly via perl or

3. Why why why?? help please

4. SQL Server Performance Problem - Good query performance, bad update performance

5. Please give opinions on how to setup disks for best performance uder SQL

6. Please give opinions on different ways to setup disks for best performance under SQL

7. FINAL NOTICE - 18 June 18 Webcast - Best Practices for DB Performance, M. Lurie

8. FINAL NOTICE - Today's Webcast - Best Practices for DB Performance, M. Lurie

9. Best performance for importing text file to DB?

10. Good, simple, robust VB DB programming examples, please?

11. Why is IFMX stock doing poorly?

12. help figuring out why query doing full table scan


 
Powered by phpBB® Forum Software