[PERFORM] what does "initplan" operation in explain output mean?
We are running 8.3.10 64bit.
This message is a request for information about the "initplan" operation in
explain plan.
I want to know if I can take advantage of it, and use it to initialize
query-bounds for the purpose of enforcing constraint exclusion on a table which
has been range-partitioned on a serial-id column.
Compare the plans below.
They all do the same thing and delete from a table named work_active (about
500rows), which is a subset of work_unit (about 50m rows).
Stmt_3 is the plan currently in use.
Stmt_4 and stmt_5 ilustrate explain plans of two variants of stmt_3 (no
partitions yet):
- Limit the sub-query using constants (derived from a prior query
min() and max() against work_active), (ref stmt_4 below) or
- Try and do something cute and do a subquery using min() and max()
(ref stmt_5 below).
My questions are:
- What does the "initplan" operation do? ( I can take a guess, but
could someone give me some details, cos the docn about it is pretty sparse).
- Will this enable constraint exclusion on the work_unit table if we
introduce partitioning?
Thanks in adv for any help you can give me.
Mr
caesius=# \i stmt_3.sql
explain
DELETE FROM work_active wa
WHERE EXISTS (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.run_id = r.id
AND(( (wu.status not in (2,3)) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..23078.82 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.53 rows=1 width=0)
-> Nested Loop (cost=0.00..30.53 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR
(wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu
(cost=0.00..19.61 rows=1 width=16)
Index Cond: (id = $0)
-> Index Scan using run_pkey on run r (cost=0.00..10.91
rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_4.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.id between 100 and 110
ANDwu.run_id = r.id
AND(( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..22624.37 rows=362 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.54 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR
(wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu
(cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= 100) AND (id <= 110) AND (id
= $0))
-> Index Scan using run_pkey on run r (cost=0.00..10.91
rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_5.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.id between (select min(wu_id) from work_active limit 1) and
(select max(wu_id) from work_active limit 1)
ANDwu.run_id = r.id
AND(( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..35071.47 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=16.22..46.76 rows=1 width=0)
InitPlan
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan using work_active_pkey on work_active
(cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan Backward using work_active_pkey on
work_active (cost=0.00..5987.09 rows=739 w
Re: [PERFORM] what does "initplan" operation in explain output mean?
Thanks. So am I right in assuming that the aggregate sub-query ( against work_active ) results will not assist with constraint exclusion in the sub-query against work_unit (if we introduce range partitions on this table)? Mr -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Sunday, August 01, 2010 7:08 AM To: Mark Rostron Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] what does "initplan" operation in explain output mean? Mark Rostron writes: > This message is a request for information about the "initplan" operation in > explain plan. An initplan is a sub-SELECT that only needs to be executed once because it has no dependency on the immediately surrounding query level. The cases you show here are from sub-SELECTs like this: (select min(wu_id) from work_active limit 1) which yields a value that's independent of anything in the outer query. If there were an outer reference in there, you'd get a SubPlan instead, because the subquery would need to be done over again for each row of the outer query. BTW, adding LIMIT 1 to an aggregate query is pretty pointless. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] what does "initplan" operation in explain output mean?
We are running 8.3.10 64bit.
Compare the plans below.
They all do the same thing and delete from a table named work_active (about
500rows), which is a subset of work_unit (about 50m rows).
I want to introduce range-partitions on work_unit.id column (serial pk), and I
want constraint exclusion to be used.
Stmt_3 is the plan currently in use.
Stmt_4 and stmt_5 compare explain plans of two variants of the stmt (no
partitions yet):
- Limit the sub-query using constants (derived from a prior query of
min() and max() against work_active), (ref stmt_4 below) or
- Try and do something cute and do a subquery using min() and max()
(ref stmt_5 below).
My questions are:
- What does the "initplan" operation do? ( I can take a guess, but
could someone give me some details, cos the docn about it is pretty sparse).
- Will this enable constraint exclusion on the work_unit table if we
introduce partitioning?
Thanks in adv for any help you can give me.
Mr
caesius=# \i stmt_3.sql
explain
DELETE FROM work_active wa
WHERE EXISTS (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.run_id = r.id
AND(( (wu.status not in (2,3)) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..23078.82 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.53 rows=1 width=0)
-> Nested Loop (cost=0.00..30.53 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR
(wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu
(cost=0.00..19.61 rows=1 width=16)
Index Cond: (id = $0)
-> Index Scan using run_pkey on run r (cost=0.00..10.91
rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_4.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.id between 100 and 110
ANDwu.run_id = r.id
AND(( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..22624.37 rows=362 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.54 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR
(wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu
(cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= 100) AND (id <= 110) AND (id
= $0))
-> Index Scan using run_pkey on run r (cost=0.00..10.91
rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_5.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
ANDwu.id between (select min(wu_id) from work_active limit 1) and
(select max(wu_id) from work_active limit 1)
ANDwu.run_id = r.id
AND(( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR
(r.status > 2) )
LIMIT 1
);
QUERY PLAN
Seq Scan on work_active wa (cost=0.00..35071.47 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=16.22..46.76 rows=1 width=0)
InitPlan
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan using work_active_pkey on work_active
(cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan Backward using work_active_pkey on
work_active (cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Nested Loop (cost=0.00..3
Re: [PERFORM] Very poor performance
This is weird - is there a particular combination of memberid/answered in
answerselectindex that has a very high rowcount?
First change I would suggest looking into would be to try changing sub-query
logic to check existence and limit the result set of the sub-query to a single
row
Select distinct(m.id)
>From member m
Where exists (
Select 1
From answerselectinstance a
Where a.member_id = m.id
And a.answerid between 127443 and 127448
Limit 1
)
If member.id is a primary key, you can eliminate the "distinct" i.e. the sort.
Second would be to build a partial index on answersselectindex to index only
the memberid's you are interested in:
"Create index on answersselectindex(memberid) where answerid
between 127443 and 127448"
Mr
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Aaron Burnett
Sent: Monday, August 16, 2010 6:07 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Very poor performance
Hi,
I'm hoping someone can offer some help here. The query and explain analyze and
table layout are below and attached in a text file if the formatting is bad.
The query is part of a bigger query that our front end runs. This is the part
that takes forever (84 minutes in this case) to finish and more often than not
the front end times out. The table (answerselectinstance) has 168664317 rows
while the member table has 626435 rows.
Postgres Version 8.25
CentOs 5.2
16 Gig RAM
192MB work_mem (increasing to 400MB didn't change the outcome)
very light use on this server, it ais a slave to a slony replicated
master/slave setup.
Again, apologies if the formatting got munged, the attached text file has the
same info.
Thanking you in advance for any help and suggestions.
Aaron
explain analyze select distinct(id) from member where id in (select memberid
from answerselectinstance where nswerid = 127443 OR answerid = 127444 OR
answerid = 127445 OR answerid = 127446 OR answerid = 127447 OR answerid =
127448 ) ;
LOG: duration: 5076038.709 ms statement: explain analyze select distinct(id)
from member where id in (select memberid from answerselectinstance where
answerid = 127443 OR answerid = 127444 OR answerid = 127445 OR answerid =
127446 OR answerid = 127447 OR answerid = 127448 ) ;
QUERY PLAN
--
Unique (cost=101357.24..101357.28 rows=9 width=4) (actual
time=5075511.974..5075911.077 rows=143520 loops=1)
-> Sort (cost=101357.24..101357.26 rows=9 width=4) (actual
time=5075511.971..5075644.323 rows=143520 loops=1)
Sort Key: member.id
-> Nested Loop IN Join (cost=0.00..101357.10 rows=9 width=4) (actual
time=19.867..5075122.724 rows=143520 loops=1)
-> Seq Scan on member (cost=0.00..78157.65 rows=626265
width=4) (actual time=3.338..2003.582 rows=626410 loops=1)
-> Index Scan using asi_memberid_idx on answerselectinstance
(cost=0.00..444.46 rows=9 width=4) (actual time=8.096..8.096 rows=0
loops=626410)
Index Cond: (member.id = answerselectinstance.memberid)
Filter: ((answerid = 127443) OR (answerid = 127444) OR
(answerid = 127445) OR (answerid = 127446) OR (answerid = 127447) OR (answerid
= 127448))
Total runtime: 5076034.203 ms
(9 rows)
Column |Type |
Modifiers
+-+
memberid | integer | not null
answerid | integer | not null
taskinstanceid | integer | not null default 0
created| timestamp without time zone | default "timestamp"('now'::text)
id | integer | not null default
nextval(('"asi_id_seq"'::text)::regclass)
Indexes:
"asi_pkey" PRIMARY KEY, btree (id)
"asi_answerid_idx" btree (answerid)
"asi_memberid_idx" btree (memberid)
"asi_taskinstanceid_idx" btree (taskinstanceid)
Triggers:
_bzzprod_cluster_denyaccess_301 BEFORE INSERT OR DELETE OR UPDATE ON
answerselectinstance FOR EACH ROW EXECUTE PROCEDURE
_bzzprod_cluster.denyaccess('_bzzprod_cluster')
Re: [PERFORM] Very poor performance
So, building the partial index will avoid the table lookup. Currently answerselectindex only has single-column indexes on memberid and answerid, so any query with a predicate on both columns is gonna be forced to do an index lookup on one column followed by a table lookup to get the other one (which is what the plan shows). This will be slower than if you can get it to lookup only an index. I suggested a partial index (and not a two-column index) to keep it small, and to reduce the likelihood that it will screw up another query. Anyway - good luck man. ? From: Aaron Burnett [mailto:aburn...@bzzagent.com] Sent: Monday, August 16, 2010 7:20 PM To: Mark Rostron; pgsql-performance@postgresql.org Subject: RE: Very poor performance Thanks Mark, Yeah, I apologize, I forgot to mention a couple of things. m.id is the primary key but the biggest problem is that the query loops 626410 times because at one time people were allowed to delete member.id rows which now will break the application if the a.memberid comes out and it doesn't exist in the member table. The version you sent me yields pretty much the same results. All I really SHOULD have to do is query the a.memberid column to get distinct memberid and the query takes less than 2 seconds. The join to the member table and subsequnt 600K loops are the killer. The answerselectinstance table has 166 million rows... so the math is pretty easy on why it's painfully slow. Other than delting data in the answerselectinstance table to get rid of the orphan memberid's I was hoping someone had a better way to do this. -Original Message----- From: Mark Rostron [mailto:mrost...@ql2.com] Sent: Mon 8/16/2010 9:51 PM To: Aaron Burnett; pgsql-performance@postgresql.org Subject: RE: Very poor performance This is weird - is there a particular combination of memberid/answered in answerselectindex that has a very high rowcount? First change I would suggest looking into would be to try changing sub-query logic to check existence and limit the result set of the sub-query to a single row Select distinct(m.id) >From member m Where exists ( Select 1 From answerselectinstance a Where a.member_id = m.id And a.answerid between 127443 and 127448 Limit 1 ) If member.id is a primary key, you can eliminate the "distinct" i.e. the sort. Second would be to build a partial index on answersselectindex to index only the memberid's you are interested in: "Create index on answersselectindex(memberid) where answerid between 127443 and 127448" Mr From: pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Aaron Burnett Sent: Monday, August 16, 2010 6:07 PM To: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org> Subject: [PERFORM] Very poor performance Hi, I'm hoping someone can offer some help here. The query and explain analyze and table layout are below and attached in a text file if the formatting is bad. The query is part of a bigger query that our front end runs. This is the part that takes forever (84 minutes in this case) to finish and more often than not the front end times out. The table (answerselectinstance) has 168664317 rows while the member table has 626435 rows. Postgres Version 8.25 CentOs 5.2 16 Gig RAM 192MB work_mem (increasing to 400MB didn't change the outcome) very light use on this server, it ais a slave to a slony replicated master/slave setup. Again, apologies if the formatting got munged, the attached text file has the same info. Thanking you in advance for any help and suggestions. Aaron explain analyze select distinct(id) from member where id in (select memberid from answerselectinstance where nswerid = 127443 OR answerid = 127444 OR answerid = 127445 OR answerid = 127446 OR answerid = 127447 OR answerid = 127448 ) ; LOG: duration: 5076038.709 ms statement: explain analyze select distinct(id) from member where id in (select memberid from answerselectinstance where answerid = 127443 OR answerid = 127444 OR answerid = 127445 OR answerid = 127446 OR answerid = 127447 OR answerid = 127448 ) ; QUERY PLAN -- Unique (cost=101357.24..101357.28 rows=9 width=4) (actual time=5075511.974..5075911.077 rows=143520 loops=1) -> Sort (cost=101357.24..101357.26 rows=9 width=4) (actual time=5075511.971..5075644.323 rows=143520 loops=1) Sort Key: member.id -> Nested Loop IN Join (cost=0.00..101357.10 rows=9 width=4) (actual time=19.867..5075122.724 rows=143520 loops=1) -> Seq Scan on membe
[PERFORM] interpret statement log duration information
Hey Turned on log_min_duration_statement today and started getting timings on sql statements (version 8.3.10). Can anyone please tell me how to interpret the (S_nn/C_nn) information in the log line. LOG: duration: 19817.211 ms execute S_73/C_74: (statement text) . Thanks for your time Mr
[PERFORM] questions regarding shared_buffers behavior
Question regarding the operation of the shared_buffers cache and implications of the pg_X_stat_tables|pg_X_stat_indexes stats. ( I am also aware that this is all complicated by the kernel cache behavior, however, if, for the purpose of these questions, you wouldn't mind assuming that we don't have a kernel cache, and therefore just focus on the behavior of the db cache as an isolated component, it will help - thanks in advance). What is the procedure that postgres uses to decide whether or not a table/index block will be left in the shared_buffers cache at the end of the operation? Are there any particular types of *table* access operations that will cause postgres to choose not to retain the table pages in shared_buffers at the end of the operation? In particular, the activity tracked by: - Seq_scan - Seq_tup_read - Idx_tup_read - Idx_tup_fetch Are there any particular types of *index* access operations that will cause postgres to choose not to retain the index pages in shared_buffers at the end of the operation? In particular, the activity tracked by: - idx_scan - Idx_tup_read - Idx_tup_fetch
Re: [PERFORM] questions regarding shared_buffers behavior
> > > > What is the procedure that postgres uses to decide whether or not a > > table/index block will be left in the shared_buffers cache at the end > > of the operation? > > > > The only special cases are for sequential scans and VACUUM, which use > continuously re-use a small section of the buffer cache in some cases instead. Thanks - the part about sequential scans and the re-use of a small section of shared_buffers is the bit I was interested in. I don't suppose you would be able to tell me how large that re-useable area might be? Now, with regard to the behavior of table sequential scans: do the stat values in seq_scan and seq_tup_read reflect actual behavior. I assume they do, but I'm just checking - these would be updated as the result of real I/O as opposed to fuzzy estimates? Obviously, the reason I am asking this is that I am noticing high machine io levels that would only result from sequential scan activity. The explain output says otherwise, but the seq_scan stat value for the table kinda correlates. Hence my enquiry. Thanks in advance. Mr -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan
Hi
My question is: Was there any major optimizer change between 8.3.10 to
8.3.14?
I'm getting a difference in explain plans that I need to account for.
We are running production pg8.3.10, and are considering upgrading to 8.4.x
(maybe 9.0), because we expected to benefit from some of the performance fixes
of 8.4, in particular the improved use of the posix fadvise on bitmap index
scans, mentioned in the 8.4.0 release notes.
So, I installed the latest 8.3.14 and did a comparison query between the test
machine and prod 8.3.10, to establish a machine power difference.
To do this, I am running a query across two pg 8.3.x installs - prod 8.3.10 and
new 8.3.14.
The database used as the test in each instance is a new database, with an
identical data import on both.
The 8.3.10 prod machine is a faster cpu ( prod 8.3.10: 3ghz intel E5700,
8.3.14: 2.2ghz intel E5345 (and less ram)).
The memory settings (shared_buffers, effective_cache_size, work_mem,
maintenance_work_mem) are equal.
The results are against repeated queries, so there is no I/O component in the
comparison - it is simply cpu and memory.
So, I expected the query response on 8.3.14 to be slower, due to being on a
less powerful machine.
However, not so: I am actually getting a faster result on the 8.3.14
installation (in spite of the machine being less powerful).
Looking at the explain plan, something changed.
For some reason, the "index scan" and "index cond" ops used by 8.3.10 are
replaced by a "bitmap index scan" and "index cond" in the 8.3.14.
I'm pretty sure this is giving me the better result in 8.3.14.
(in spite of the reduced machine power).
Obviously this result is quite unexpected and I am trying to work out why.
(The only other mention that I have seen of bitmap index scan improvements was
in the 8.4.0 release notes).
So, I am looking for information as to why this change occurred.
I reckon either it is a real version difference between 8.3.10 and 8.3.14, or
else a difference in configuration.
Does anyone have any comments?
The 8.3.10 plan is:
explain select * from view_v1 where action_date between '2010-10-01' and
'2010-12-08'
QUERY PLAN
--
Hash Join (cost=755.53..546874.02 rows=3362295 width=99)
Hash Cond: (gp.ql2_id = dt.ql2_id)
-> Index Scan using gpn_nk_1 on data_stuff_new gp (cost=0.00..495732.14
rows=4465137 width=40)
Index Cond: ((action_date >= '2010-10-01'::date) AND (action_date <=
'2010-12-08'::date))
Filter: (action_hour = ANY ('{8,10,11,12,13,14,15,16}'::integer[]))
-> Hash (cost=720.80..720.80 rows=2779 width=67)
-> Hash Join (cost=561.38..720.80 rows=2779 width=67)
Hash Cond: (dtxgm.ql2_id = dt.ql2_id)
-> Seq Scan on data_thindt_xref_group_membership dtxgm
(cost=0.00..93.41 rows=2779 width=10)
Filter: (org_id = 1288539986)
-> Hash (cost=451.17..451.17 rows=8817 width=57)
-> Seq Scan on data_thing dt (cost=0.00..451.17
rows=8817 width=57)
(12 rows)
The plan on 8.3.14 is:
explain select * from view_v1 where action_date between '2010-10-01' and
'2010-12-08'
QUERY PLAN
-
Hash Join (cost=190151.42..684420.67 rows=3403329 width=99)
Hash Cond: (gp.ql2_id = dt.ql2_id)
-> Bitmap Heap Scan on data_stuff_new gp (cost=189395.38..633046.20
rows=4471358 width=40)
Recheck Cond: ((action_date >= '2010-10-01'::date) AND (action_date <=
'2010-12-08'::date))
Filter: (action_hour = ANY ('{8,10,11,12,13,14,15,16}'::integer[]))
-> Bitmap Index Scan on gpn_nk_1 (cost=0.00..188277.54 rows=7090513
width=0)
Index Cond: ((action_date >= '2010-10-01'::date) AND
(action_date <= '2010-12-08'::date))
-> Hash (cost=721.13..721.13 rows=2793 width=67)
-> Hash Join (cost=561.38..721.13 rows=2793 width=67)
Hash Cond: (dtxgm.ql2_id = dt.ql2_id)
-> Seq Scan on data_thindt_xref_group_membership dtxgm
(cost=0.00..93.41 rows=2793 width=10)
Filter: (org_id = 1288539986)
-> Hash (cost=451.17..451.17 rows=8817 width=57)
-> Seq Scan on data_thing dt (cost=0.00..451.17
rows=8817 width=57)
(14 rows)
Re: [PERFORM] comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan
I found the difference. Random_page_cost is 1 in the production 8.3.10, I guess weighting the decision to use "index scan". Thanks for the replies, gentlemen. > If you diff the postgresql.conf files for both installs, what's different? In the list below, 8.3.10 parameter value is in the clear, (8.3.14 is in brackets) Max_fsm_pages 819200 vs (204800) Max_fsm_relations 4000 vs (dflt 1000) Synchronous_commit off vs (dflt on) Wal_buffers 256kb vs (dflt 64kb) Checkpoint_segments 128 vs (dflt 3) Random_page_cost 1 vs (dflt 4)#!!! Actually this is the difference in the explain plans Constraint_exclusion on vs (dflt off) a bunch of logging parameters have been set Autovacuum_freeze_max_age 9 vs (dflt 2) vacuum_freeze_min_age = 5000 vs (dflt 1) deadlock_timeout = 20s (vs dflt 1s) add_missing_from = on (vs dflt off) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan
> It would be easier to suggest what might be wrong if you included "EXPLAIN > ANALYZE" output instead of just EXPLAIN. > It's not obvious whether 8.3 or 8.4 is estimating things better. Thanks for reply man Turns out random_page_cost was set low in the 8.3.10 version - when I reset it to 4(dflt), the explain plans are the same. We'll double check our other queries, and then I'll see if I can reset it to dflt for the database.
Re: [PERFORM] amazon ec2
iowait is a problem on any platform that relies on spinning media, compared to RAM. no matter how fast a disk is, and no matter how intelligent the controller is, you are still dealing with an access speed differential of 10^6 (speed of disk access compared to memory access). i have had good results by avoiding it. if you can do this, ec2 is not too shabby, but beware - it doesn't come free. this is achievable under the following circumstances (and maybe there are other ways to do this). i use a technique of pro-actively querying enough of my anticipated result set with a daemon procedure. as long as the frequency of your query daemon execution is greater than that of the competitor processes (eg ETL and other update activity), AND a substantial part of the result set will fit in available RAM, then the result set will be served from file system cache at the time you want it. i have found that it doesn't take much to get this to happen, once you have identified your critical result set. like - you can get away with running it once/hour, and i'm still reducing the frequency and getting good results. this approach basically assumes a 90/10 rule - at any point in time, you only want to access 10% of your data. if you can work out what the 10% is, and it will fit into RAM, then you can set it up to cache it. it also imposes no additional cost in ec2, because Amazon doesn't bill you for CPU activity, although the large-RAM machines do cost more. Depends on how big your critical result set is, and how much speed you need. dont know if this helps - the success/failure of it depends on your typical query activity, the size of your critical result set, and whether you are able to get enough RAM to make this work. as i said it doesn't come for free, but you can make it work. as a further point, try also checking out greenplum - it is an excellent postgres derivative with a very powerful free version. the reason why i bring it up is because it offers block-level compression (with caveats - it also doesn't come for free, so do due diligence and rtfm carefully). The compression enabled me to improve the cache hit rate, and so you further reduce the iowait problem. greenplum is also a better parallel machine than postgres, so combining the cache technique above with greenplum compression and parallel query, i have been able to get 20:1 reduction in response times for some of our queries. obviously introducing new database technology is a big deal, but we needed the speed, and it kinda worked. mr On Tue, May 3, 2011 at 1:09 PM, Alan Hodgson wrote: > On May 3, 2011 12:43:13 pm you wrote: > > On May 3, 2011, at 8:41 PM, Alan Hodgson wrote: > > > I am also interested in tips for this. EBS seems to suck pretty bad. > > > > Alan, can you elaborate? Are you using PG on top of EBS? > > > > Trying to, yes. > > Let's see ... > > EBS volumes seem to vary in speed. Some are relatively fast. Some are > really > slow. Some fast ones become slow randomly. Some are fast attached to one > instance, but really slow attached to another. > > Fast being a relative term, though. The fast ones seem to be able to do > maybe > 400 random IOPS. And of course you can only get about 80MB/sec sequential > access to them on a good day. > > Which is why I'm interested in how other people are doing it. So far EC2 > doesn't seem well suited to running databases at all. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
