2010/6/4 <tv@xxxxxxxx>: >> I am reposting as my original query was mangled >> >> The link to the explain plan is here as it does not paste well into >> the email body. >> >> http://explain.depesz.com/s/kHa >> >> >> The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K >> single raid-10 array >> >> 1G work_mem >> default_statistics_target=1000 >> random_page_cost=1 > > Are you sure it's wise to set the work_mem to 1G? Do you really need it? > Don't forget this is not a 'total' or 'per query' - each query may > allocate multiple work areas (and occupy multiple GB). But I guess this > does not cause the original problem. > > The last row 'random_page_cost=1' - this basically says that reading data > by random is just as cheap as reading data sequentially. Which may result > in poor performance due to bad plans. Why have you set this value? > > Sure, there are rare cases where 'random_page_cost=1' is OK. The default for 8.4 is 2 I tried with 2 and 1..but the results are not very different. I understand that for fast disks (which we have with a decent Raid 10 setup)..the random_page_cost can be lowered as needed..but I guess it did not make a difference here. > >> >> I am curious why the hash join takes so long. The main table >> dev4_act_dy_fact_2010_05_t has 25 million rows. The table is >> partitioned into 3 parts per month. Remaining tables are very small ( >> < 1000 rows) > > Well, the real cause that makes your query slow is the 'index scan' part. > > Index Scan using dev4_act_dy_fact_2010_05_t3_thedate on > dev4_act_dy_fact_2010_05_t3 a (cost=0.00..94041.89 rows=204276 width=60) > (actual time=164533.725..164533.725 rows=0 loops=1) > > The first thing to note here is the difference in expected and actual > number of rows - the planner expects 204276 but gets 0 rows. How large is > this partition? The partition has 25 million rows with indexes on theDate, node_id.. I altered the random_page_cost to 4 (1 more than the default)..still slow. These tables are analyzed every day I have an index on each field used in the where criteria, > > Try to analyze it, set the random_page_cost to something reasonable (e.g. > 4) and try to run the query again. > > Tomas > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
