Re: [pgsql-performance] Is dump-reload the only cure?
От | |
---|---|
Тема | Re: [pgsql-performance] Is dump-reload the only cure? |
Дата | |
Msg-id | 1068.203.145.130.142.1036156522.squirrel@mail.trade-india.com обсуждение исходный текст |
Ответ на | Is dump-reload the only cure? (<mallah@trade-india.com>) |
Список | pgsql-admin |
Rod , Clustering did work for my other case ;-) tradein_clients=> explain analyze SELECT count(*) from email_source where source_id=173; NOTICE: QUERY PLAN: Aggregate (cost=13042.91..13042.91 rows=1 width=0) (actual time=1415.32..1415.32 rows=1 loops=1) -> Seq Scan on email_source (cost=0.00..12964.48 rows=31375 width=0) (actual time=1.19..1368.58 rows=32851 loops=1)Total runtime: 1415.42 msec EXPLAIN tradein_clients=> \d email_source Table "email_source" Column | Type | Modifiers -----------+---------+----------- email_id | integer | source_id | integer | Indexes: email_source_sid Unique keys: email_source_idx tradein_clients=> CLUSTER email_source_sid on email_source ; CLUSTER tradein_clients=> tradein_clients=> explain analyze SELECT count(*) from email_source where source_id=173; NOTICE: QUERY PLAN: Aggregate (cost=11458.83..11458.83 rows=1 width=0) (actual time=207.73..207.73 rows=1 loops=1) -> Index Scan using email_source_sid on email_source (cost=0.00..11449.76 rows=3627 width=0) (actual time=0.27..161.04 rows=32851 loops=1)Total runtime: 207.90 msec EXPLAIN Does it Mean that clustered indexes are guarrented to be used for index scan? one more thing does clustering means that all future data addition will happen in the ordered manner only i mean consecutively in terms of source_id? Regds MALLAH. > On Fri, 2002-11-01 at 06:15, mallah@trade-india.com wrote: > > > Looks like a borderline case. See the costs of the index scan and sequential scan are very > similar. Since 499 covers nearly 1 in 10 tuples, it's likely found on nearly every page. This > should make a sequential scan much cheaper. > > However, if the data is clumped together (not distributed throughout the table) than an index > scan may be preferable. So... CLUSTER may be useful to you. > > In the future please 'explain analyze' the queries you're looking at to see actual costs as > compared to the estimated cost. > > >> 499 | 25010 >> 501 | 3318 >> >> >> before dump reload: >> tradein_clients=# VACUUM VERBOSE ANALYZE email_bank_mailing_lists; NOTICE: --Relation >> email_bank_mailing_lists-- >> NOTICE: Pages 3583: Changed 0, Empty 0; Tup 256419: Vac 0, Keep 0, UnUsed 44822. >> Total CPU 0.24s/0.04u sec elapsed 0.30 sec. >> NOTICE: Analyzing email_bank_mailing_lists >> VACUUM >> tradein_clients=# explain SELECT count( email_id ) from email_bank_mailing_lists where >> query_id=499;NOTICE: QUERY PLAN: >> >> Aggregate (cost=6863.24..6863.24 rows=1 width=4) >> -> Seq Scan on email_bank_mailing_lists (cost=0.00..6788.24 rows=30001 width=4) >> >> EXPLAIN > > -- > Rod Taylor ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
В списке pgsql-admin по дате отправления: