pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow query performance
16+ messages / 6 participants
[nested] [flat]
* slow query performance
@ 2010-06-03 17:47 Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-03 17:47 UTC (permalink / raw)
To: pgsql-performance
I cant seem to pinpoint why this query is slow . No full table scans
are being done. The hash join is taking maximum time. The table
dev4_act_action has only 3 rows.
box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
1G work_mem
20G effective_cache
random_page_cost=1
default_statistics_target=1000
The larget table in the inner query is dev4_act_dy_fact which is
partitioned into 3 partitions per month. Each partition has about 25
million rows.
The rest of the tables are very small (100- 1000 rows)
explain analyze
select ipconvert(srctest_num),CASE targetpt::character varying
WHEN NULL::text THEN serv.targetsrv
ELSE targetpt::character varying
END AS targetsrv, sesstype,hits as cons,bytes, srcz.srcarea as
srcz, dstz.dstarea as dstz from
(
select srctest_num, targetpt,targetsrv_id, sesstype_id, sum(total) as
hits, sum(bin) + sum(bout) as bts, sourcearea_id, destinationarea_id
from dev4_act_dy_fact a, dev4_act_action act where thedate between
'2010-05-22' and '2010-05-22'
and a.action_id = act.action_id and action in ('rejected','sess_rejected')
and guardid_id in (select guardid_id from dev4_act_guardid where
guardid like 'cust00%')
and node_id=(select node_id from dev4_act_node where node='10.90.100.2')
group by srctest_num,targetpt,targetsrv_id,sesstype_id,
sourcearea_id, destinationarea_id
order by (sum(bin) + sum(bout)) desc
limit 1000
) a left outer join dev4_act_dstarea dstz on a.destinationarea_id =
dstz.dstarea_id
left outer join dev4_act_srcarea srcz on a.sourcearea_id = srcz.srcarea_id
left outer join dev4_act_targetsrv serv on a.targetsrv_id = serv.targetsrv_id
left outer join dev4_sesstype proto on a.sesstype_id = proto.sesstype_id
order by bytes desc
"Nested Loop Left Join (cost=95392.32..95496.13 rows=20 width=510)
(actual time=164533.831..164533.831 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=95392.32..95473.43 rows=20
width=396) (actual time=164533.830..164533.830 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=95392.32..95455.83 rows=20
width=182) (actual time=164533.829..164533.829 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=95392.32..95410.17
rows=20 width=186) (actual time=164533.829..164533.829 rows=0
loops=1)"
" -> Limit (cost=95392.32..95392.37 rows=20
width=52) (actual time=164533.828..164533.828 rows=0 loops=1)"
" InitPlan 1 (returns $0)"
" -> Index Scan using dev4_act_node_uindx
on dev4_act_node (cost=0.00..2.27 rows=1 width=4) (actual
time=0.052..0.052 rows=0 loops=1)"
" Index Cond: ((node)::text =
'10.90.100.2'::text)"
" -> Sort (cost=95390.05..95390.10 rows=20
width=52) (actual time=164533.826..164533.826 rows=0 loops=1)"
" Sort Key: ((sum(a.bin) + sum(a.bout)))"
" Sort Method: quicksort Memory: 17kB"
" -> HashAggregate
(cost=95389.22..95389.62 rows=20 width=52) (actual
time=164533.796..164533.796 rows=0 loops=1)"
" -> Nested Loop Semi Join
(cost=7.37..95388.77 rows=20 width=52) (actual
time=164533.793..164533.793 rows=0 loops=1)"
" -> Hash Join
(cost=7.37..94836.75 rows=2043 width=56) (actual
time=164533.792..164533.792 rows=0 loops=1)"
" Hash Cond:
(a.action_id = act.action_id)"
" -> Append
(cost=2.80..94045.71 rows=204277 width=60) (actual
time=164533.790..164533.790 rows=0 loops=1)"
" -> Bitmap
Heap Scan on dev4_act_dy_fact a (cost=2.80..3.82 rows=1 width=60)
(actual time=0.064..0.064 rows=0 loops=1)"
" Recheck
Cond: ((node_id = $0) AND (thedate >= '2010-05-22 00:00:00'::timestamp
without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp
without time area))"
" ->
BitmapAnd (cost=2.80..2.80 rows=1 width=0) (actual time=0.062..0.062
rows=0 loops=1)"
"
-> Bitmap Index Scan on dev4_act_dy_dm_nd_indx (cost=0.00..1.27
rows=3 width=0) (actual time=0.062..0.062 rows=0 loops=1)"
"
Index Cond: (node_id = $0)"
"
-> Bitmap Index Scan on dev4_act_dy_dm_cd_indx (cost=0.00..1.28
rows=3 width=0) (never executed)"
"
Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp without
time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp without
time area))"
" -> 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)"
" Index
Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp without time area)
AND (thedate <= '2010-05-22 00:00:00'::timestamp without time area))"
" Filter:
(node_id = $0)"
" -> Hash
(cost=4.54..4.54 rows=2 width=4) (never executed)"
" -> Bitmap
Heap Scan on dev4_act_action act (cost=2.52..4.54 rows=2 width=4)
(never executed)"
" Recheck
Cond: ((action)::text = ANY ('{rejected,sess_rejected}'::text[]))"
" ->
Bitmap Index Scan on dev4_act_action_uindx (cost=0.00..2.52 rows=2
width=0) (never executed)"
"
Index Cond: ((action)::text = ANY
('{rejected,sess_rejected}'::text[]))"
" -> Index Scan using
dev4_act_guardid_pk on dev4_act_guardid (cost=0.00..0.27 rows=1
width=4) (never executed)"
" Index Cond:
(dev4_act_guardid.guardid_id = a.guardid_id)"
" Filter:
((dev4_act_guardid.guardid)::text ~~ 'cust00%'::text)"
" -> Index Scan using sys_c006248 on dev4_sesstype
proto (cost=0.00..0.87 rows=1 width=102) (never executed)"
" Index Cond: (a.sesstype_id = proto.sesstype_id)"
" -> Index Scan using dev4_act_targetsrv_pk on
dev4_act_targetsrv serv (cost=0.00..2.27 rows=1 width=4) (never
executed)"
" Index Cond: (a.targetsrv_id = serv.targetsrv_id)"
" -> Index Scan using dev4_act_srcarea_pk on dev4_act_srcarea
srcz (cost=0.00..0.87 rows=1 width=222) (never executed)"
" Index Cond: (a.sourcearea_id = srcz.srcarea_id)"
" -> Index Scan using dev4_act_dstarea_pk on dev4_act_dstarea dstz
(cost=0.00..0.87 rows=1 width=122) (never executed)"
" Index Cond: (a.destinationarea_id = dstz.dstarea_id)"
"Total runtime: 164534.172 ms"
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-03 18:43 ` Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Andy Colson @ 2010-06-03 18:43 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; pgsql-performance
On 6/3/2010 12:47 PM, Anj Adu wrote:
> I cant seem to pinpoint why this query is slow . No full table scans
> are being done. The hash join is taking maximum time. The table
> dev4_act_action has only 3 rows.
>
> box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
> 1G work_mem
> 20G effective_cache
> random_page_cost=1
> default_statistics_target=1000
>
> The larget table in the inner query is dev4_act_dy_fact which is
> partitioned into 3 partitions per month. Each partition has about 25
> million rows.
> The rest of the tables are very small (100- 1000 rows)
>
> explain analyze
> select ipconvert(srctest_num),CASE targetpt::character varying
> WHEN NULL::text THEN serv.targetsrv
> ELSE targetpt::character varying
> END AS targetsrv, sesstype,hits as cons,bytes, srcz.srcarea as
> srcz, dstz.dstarea as dstz from
> (
> select srctest_num, targetpt,targetsrv_id, sesstype_id, sum(total) as
> hits, sum(bin) + sum(bout) as bts, sourcearea_id, destinationarea_id
> from dev4_act_dy_fact a, dev4_act_action act where thedate between
> '2010-05-22' and '2010-05-22'
> and a.action_id = act.action_id and action in ('rejected','sess_rejected')
> and guardid_id in (select guardid_id from dev4_act_guardid where
> guardid like 'cust00%')
> and node_id=(select node_id from dev4_act_node where node='10.90.100.2')
> group by srctest_num,targetpt,targetsrv_id,sesstype_id,
> sourcearea_id, destinationarea_id
> order by (sum(bin) + sum(bout)) desc
> limit 1000
> ) a left outer join dev4_act_dstarea dstz on a.destinationarea_id =
> dstz.dstarea_id
> left outer join dev4_act_srcarea srcz on a.sourcearea_id = srcz.srcarea_id
> left outer join dev4_act_targetsrv serv on a.targetsrv_id = serv.targetsrv_id
> left outer join dev4_sesstype proto on a.sesstype_id = proto.sesstype_id
> order by bytes desc
>
>
Wow, the word wrap on that makes it hard to read... can you paste it
here and send us a link?
http://explain.depesz.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
@ 2010-06-03 20:37 ` Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-03 20:37 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
Link to plan
http://explain.depesz.com/s/kHa
On Thu, Jun 3, 2010 at 11:43 AM, Andy Colson <andy@squeakycode.net> wrote:
> On 6/3/2010 12:47 PM, Anj Adu wrote:
>>
>> I cant seem to pinpoint why this query is slow . No full table scans
>> are being done. The hash join is taking maximum time. The table
>> dev4_act_action has only 3 rows.
>>
>> box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
>> 1G work_mem
>> 20G effective_cache
>> random_page_cost=1
>> default_statistics_target=1000
>>
>> The larget table in the inner query is dev4_act_dy_fact which is
>> partitioned into 3 partitions per month. Each partition has about 25
>> million rows.
>> The rest of the tables are very small (100- 1000 rows)
>>
>> explain analyze
>> select ipconvert(srctest_num),CASE targetpt::character varying
>> WHEN NULL::text THEN serv.targetsrv
>> ELSE targetpt::character varying
>> END AS targetsrv, sesstype,hits as cons,bytes, srcz.srcarea as
>> srcz, dstz.dstarea as dstz from
>> (
>> select srctest_num, targetpt,targetsrv_id, sesstype_id, sum(total) as
>> hits, sum(bin) + sum(bout) as bts, sourcearea_id, destinationarea_id
>> from dev4_act_dy_fact a, dev4_act_action act where thedate between
>> '2010-05-22' and '2010-05-22'
>> and a.action_id = act.action_id and action in
>> ('rejected','sess_rejected')
>> and guardid_id in (select guardid_id from dev4_act_guardid where
>> guardid like 'cust00%')
>> and node_id=(select node_id from dev4_act_node where node='10.90.100.2')
>> group by srctest_num,targetpt,targetsrv_id,sesstype_id,
>> sourcearea_id, destinationarea_id
>> order by (sum(bin) + sum(bout)) desc
>> limit 1000
>> ) a left outer join dev4_act_dstarea dstz on a.destinationarea_id =
>> dstz.dstarea_id
>> left outer join dev4_act_srcarea srcz on a.sourcearea_id =
>> srcz.srcarea_id
>> left outer join dev4_act_targetsrv serv on a.targetsrv_id =
>> serv.targetsrv_id
>> left outer join dev4_sesstype proto on a.sesstype_id = proto.sesstype_id
>> order by bytes desc
>>
>>
>
>
> Wow, the word wrap on that makes it hard to read... can you paste it here
> and send us a link?
>
> http://explain.depesz.com
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-10 02:12 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-06-10 02:12 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs@gmail.com> wrote:
> Link to plan
>
> http://explain.depesz.com/s/kHa
Your problem is likely related to the line that's showing up in red:
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)
* Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp
without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp
without time area))
* Filter: (node_id = $0)
This index scan is estimated to return 204,276 rows and actually
returned zero... it might work better to rewrite this part of the
query as a join, if you can:
node_id=(select node_id from dev4_act_node where node='10.90.100.2')
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
@ 2010-06-10 02:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-06-10 02:55 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Anj Adu <fotographs@gmail.com>; Andy Colson <andy@squeakycode.net>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs@gmail.com> wrote:
>> Link to plan
>>
>> http://explain.depesz.com/s/kHa
> Your problem is likely related to the line that's showing up in red:
> 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)
> * Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp
> without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp
> without time area))
> * Filter: (node_id = $0)
"timestamp without time area"? Somehow I think this isn't the true
unaltered output of EXPLAIN.
I'm just guessing, since we haven't been shown any table schemas,
but what it looks like to me is that the planner is using an entirely
inappropriate index in which the "thedate" column is a low-order column.
So what looks like a nice tight indexscan range is actually a full-table
indexscan. The planner knows that this is ridiculously expensive, as
indicated by the high cost estimate. It would be cheaper to do a
seqscan, which leads me to think the real problem here is the OP has
disabled seqscans.
It might be worth providing an index in which "thedate" is the only, or
at least the first, column. For this particular query, an index on
node_id and thedate would actually be ideal, but that might be too
specialized.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-06-10 03:17 ` Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-10 03:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Andy Colson <andy@squeakycode.net>; pgsql-performance
The plan is unaltered . There is a separate index on theDate as well
as one on node_id
I have not specifically disabled sequential scans.
This query performs much better on 8.1.9 on a similar sized
table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
On Wed, Jun 9, 2010 at 7:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs@gmail.com> wrote:
>>> Link to plan
>>>
>>> http://explain.depesz.com/s/kHa
>
>> Your problem is likely related to the line that's showing up in red:
>
>> 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)
>> * Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp
>> without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp
>> without time area))
>> * Filter: (node_id = $0)
>
> "timestamp without time area"? Somehow I think this isn't the true
> unaltered output of EXPLAIN.
>
> I'm just guessing, since we haven't been shown any table schemas,
> but what it looks like to me is that the planner is using an entirely
> inappropriate index in which the "thedate" column is a low-order column.
> So what looks like a nice tight indexscan range is actually a full-table
> indexscan. The planner knows that this is ridiculously expensive, as
> indicated by the high cost estimate. It would be cheaper to do a
> seqscan, which leads me to think the real problem here is the OP has
> disabled seqscans.
>
> It might be worth providing an index in which "thedate" is the only, or
> at least the first, column. For this particular query, an index on
> node_id and thedate would actually be ideal, but that might be too
> specialized.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-10 13:28 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-06-10 13:28 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
> The plan is unaltered . There is a separate index on theDate as well
> as one on node_id
>
> I have not specifically disabled sequential scans.
Please do "SHOW ALL" and attach the results as a text file.
> This query performs much better on 8.1.9 on a similar sized
> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
Well that could certainly matter...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
@ 2010-06-10 15:32 ` Anj Adu <fotographs@gmail.com>
2010-06-10 16:42 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-11 02:54 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Anj Adu @ 2010-06-10 15:32 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
Attached
Thank you
On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
>> The plan is unaltered . There is a separate index on theDate as well
>> as one on node_id
>>
>> I have not specifically disabled sequential scans.
>
> Please do "SHOW ALL" and attach the results as a text file.
>
>> This query performs much better on 8.1.9 on a similar sized
>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
>
> Well that could certainly matter...
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
Attachments:
showall.txt (text/plain, 19.6K) download
add_missing_from | off | Automatically adds missing table references to FROM clauses.
allow_system_table_mods | off | Allows modifications of the structure of system tables.
archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file.
archive_mode | off | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next xlog file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_naptime | 7d | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 50ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 1000 | Minimum number of tuple updates or deletes prior to vacuum.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour_name | | Sets the Bonjour broadcast service name.
check_function_bodies | on | Check function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.5 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_segments | 128 | Sets the maximum distance in log segments between automatic WAL checkpoints.
checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s | Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
custom_variable_classes | | Sets the list of known custom variable classes.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Turns on various assertion checks.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 1000 | Sets the default statistics target.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
default_with_oids | off | Create new tables with OIDs by default.
effective_cache_size | 20GB | Sets the planner's assumption about the size of the disk cache.
effective_io_concurrency | 1 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 12 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
ignore_system_indexes | off | Disables reading from system indexes.
integer_datetimes | on | Datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_srvname | postgres | Sets the name of the Kerberos service.
lc_collate | en_US.UTF-8 | Shows the collation order locale.
lc_ctype | en_US.UTF-8 | Shows the character classification and case conversion locale.
lc_messages | en_US.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | en_US.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | en_US.UTF-8 | Sets the locale for formatting numbers.
lc_time | en_US.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
local_preload_libraries | | Lists shared libraries to preload into each backend.
log_autovacuum_min_duration | -1 | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | off | Logs each checkpoint.
log_connections | off | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_disconnections | off | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | | Controls information prefixed to each log line.
log_lock_waits | off | Logs long lock waits.
log_min_duration_statement | -1 | Sets the minimum execution time above which statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | debug1 | Sets the message levels that are logged.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_rotation_age | 1d | Automatic log file rotation will occur after N minutes.
log_rotation_size | 10MB | Automatic log file rotation will occur after N kilobytes.
log_statement | none | Sets the type of statements logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | -1 | Log the use of temporary files larger than this number of kilobytes.
log_timezone | GMT | Sets the time zone to use in log messages.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem | 400MB | Sets the maximum memory to be used for maintenance operations.
max_connections | 1000 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_stack_depth | 8MB | Sets the maximum stack depth, in kilobytes.
password_encryption | on | Encrypt passwords.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Waits N seconds on connection startup after authentication.
pre_auth_delay | 0 | Waits N seconds on connection startup before authentication.
random_page_cost | 3 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
regex_flavor | advanced | Sets the regular expression "flavor".
search_path | "$user",public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Sets the server (database) character set encoding.
server_version | 8.4.0 | Shows the server version.
server_version_num | 80400 | Shows the server version as an integer.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 1600MB | Sets the number of shared memory buffers used by the server.
silent_mode | off | Runs the server silently.
sql_inheritance | on | Causes subtables to be included by default in various commands.
ssl | off | Enables SSL connections.
standard_conforming_strings | off | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | on | Sets immediate fsync at commit.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
temp_buffers | 51200 | Sets the maximum number of temporary buffers used by each session.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | GMT | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 1024 | Sets the size reserved for pg_stat_activity.current_query, in bytes.
track_counts | on | Collects statistics on database activity.
track_functions | none | Collects function-level statistics on database activity.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 511 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 64kB | Sets the number of disk-page buffers in shared memory for WAL.
wal_segment_size | 16MB | Shows the number of pages per write ahead log segment.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | WAL writer sleep time between WAL flushes.
work_mem | 600MB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-10 16:42 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 16:58 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-06-10 16:42 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu <fotographs@gmail.com> wrote:
> Attached
Hmm. Well, I'm not quite sure what's going on here, but I think you
must be using a modified verison of PostgreSQL, because, as Tom
pointed out upthread, we don't have a data type called "timestamp with
time area". It would be called "timestamp with time zone".
Can we see the index and table definitions of the relevant tables
(attached as a text file) and the size of each one (use select
pg_relation_size('name'))?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 16:42 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
@ 2010-06-10 16:58 ` Anj Adu <fotographs@gmail.com>
2010-06-11 00:49 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-10 16:58 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
you are right..the word "zone" was replaced by "area" (my bad )
everything else is as is.
Apologies for the confusion.
On Thu, Jun 10, 2010 at 9:42 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu <fotographs@gmail.com> wrote:
>> Attached
>
> Hmm. Well, I'm not quite sure what's going on here, but I think you
> must be using a modified verison of PostgreSQL, because, as Tom
> pointed out upthread, we don't have a data type called "timestamp with
> time area". It would be called "timestamp with time zone".
>
> Can we see the index and table definitions of the relevant tables
> (attached as a text file) and the size of each one (use select
> pg_relation_size('name'))?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 16:42 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 16:58 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-11 00:49 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Robert Haas @ 2010-06-11 00:49 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
On Thu, Jun 10, 2010 at 12:58 PM, Anj Adu <fotographs@gmail.com> wrote:
> you are right..the word "zone" was replaced by "area" (my bad )
>
> everything else is as is.
>
> Apologies for the confusion.
Well, two different people have asked you for the table and index
definitions now, and you haven't provided them... I think it's going
to be hard to troubleshoot this without seeing those definitions (and
also the sizes, which I asked for in my previous email).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-11 02:54 ` Anj Adu <fotographs@gmail.com>
2010-06-11 12:44 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-11 02:54 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
I changed random_page_cost=4 (earlier 2) and the performance issue is gone
I am not clear why a page_cost of 2 on really fast disks would perform badly.
Thank you for all your help and time.
On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@gmail.com> wrote:
> Attached
>
> Thank you
>
>
> On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
>>> The plan is unaltered . There is a separate index on theDate as well
>>> as one on node_id
>>>
>>> I have not specifically disabled sequential scans.
>>
>> Please do "SHOW ALL" and attach the results as a text file.
>>
>>> This query performs much better on 8.1.9 on a similar sized
>>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
>>
>> Well that could certainly matter...
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise Postgres Company
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 02:54 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-11 12:44 ` Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:23 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kenneth Marshall @ 2010-06-11 12:44 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
Hi Anj,
That is an indication that your system was less correctly
modeled with a random_page_cost=2 which means that the system
will assume that random I/O is cheaper than it is and will
choose plans based on that model. If this is not the case,
the plan chosen will almost certainly be slower for any
non-trivial query. You can put a 200mph speedometer in a
VW bug but it will never go 200mph.
Regards,
Ken
On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
>
> I am not clear why a page_cost of 2 on really fast disks would perform badly.
>
> Thank you for all your help and time.
>
> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@gmail.com> wrote:
> > Attached
> >
> > Thank you
> >
> >
> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
> >>> The plan is unaltered . There is a separate index on theDate as well
> >>> as one on node_id
> >>>
> >>> I have not specifically disabled sequential scans.
> >>
> >> Please do "SHOW ALL" and attach the results as a text file.
> >>
> >>> This query performs much better on 8.1.9 on a similar sized
> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
> >>
> >> Well that could certainly matter...
> >>
> >> --
> >> Robert Haas
> >> EnterpriseDB: http://www.enterprisedb.com
> >> The Enterprise Postgres Company
> >>
> >
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 02:54 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 12:44 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
@ 2010-06-11 13:23 ` Anj Adu <fotographs@gmail.com>
2010-06-11 13:28 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 16+ messages in thread
From: Anj Adu @ 2010-06-11 13:23 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
Is there a way to determine a reasonable value for random_page_cost
via some testing with OS commands. We have several postgres databases
and determining this value on a case by case basis may not be viable
(we may have to go with the defaults)
On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> Hi Anj,
>
> That is an indication that your system was less correctly
> modeled with a random_page_cost=2 which means that the system
> will assume that random I/O is cheaper than it is and will
> choose plans based on that model. If this is not the case,
> the plan chosen will almost certainly be slower for any
> non-trivial query. You can put a 200mph speedometer in a
> VW bug but it will never go 200mph.
>
> Regards,
> Ken
>
> On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
>> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
>>
>> I am not clear why a page_cost of 2 on really fast disks would perform badly.
>>
>> Thank you for all your help and time.
>>
>> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@gmail.com> wrote:
>> > Attached
>> >
>> > Thank you
>> >
>> >
>> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
>> >>> The plan is unaltered . There is a separate index on theDate as well
>> >>> as one on node_id
>> >>>
>> >>> I have not specifically disabled sequential scans.
>> >>
>> >> Please do "SHOW ALL" and attach the results as a text file.
>> >>
>> >>> This query performs much better on 8.1.9 on a similar sized
>> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
>> >>
>> >> Well that could certainly matter...
>> >>
>> >> --
>> >> Robert Haas
>> >> EnterpriseDB: http://www.enterprisedb.com
>> >> The Enterprise Postgres Company
>> >>
>> >
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 02:54 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 12:44 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:23 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
@ 2010-06-11 13:28 ` Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:49 ` Re: slow query performance Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 16+ messages in thread
From: Kenneth Marshall @ 2010-06-11 13:28 UTC (permalink / raw)
To: Anj Adu <fotographs@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
If you check the archives, you will see that this is not easy
to do because of the effects of caching. The default values
were actually chosen to be a good compromise between fully
cached in RAM and totally un-cached. The actual best value
depends on the size of your database, the size of its working
set, your I/O system and your memory. The best recommendation
is usually to use the default values unless you know something
about your system that moves it out of that arena.
Regards,
Ken
On Fri, Jun 11, 2010 at 06:23:31AM -0700, Anj Adu wrote:
> Is there a way to determine a reasonable value for random_page_cost
> via some testing with OS commands. We have several postgres databases
> and determining this value on a case by case basis may not be viable
> (we may have to go with the defaults)
>
> On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> > Hi Anj,
> >
> > That is an indication that your system was less correctly
> > modeled with a random_page_cost=2 which means that the system
> > will assume that random I/O is cheaper than it is and will
> > choose plans based on that model. If this is not the case,
> > the plan chosen will almost certainly be slower for any
> > non-trivial query. You can put a 200mph speedometer in a
> > VW bug but it will never go 200mph.
> >
> > Regards,
> > Ken
> >
> > On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
> >> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
> >>
> >> I am not clear why a page_cost of 2 on really fast disks would perform badly.
> >>
> >> Thank you for all your help and time.
> >>
> >> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@gmail.com> wrote:
> >> > Attached
> >> >
> >> > Thank you
> >> >
> >> >
> >> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> >> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
> >> >>> The plan is unaltered . There is a separate index on theDate as well
> >> >>> as one on node_id
> >> >>>
> >> >>> I have not specifically disabled sequential scans.
> >> >>
> >> >> Please do "SHOW ALL" and attach the results as a text file.
> >> >>
> >> >>> This query performs much better on 8.1.9 on a similar sized
> >> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
> >> >>
> >> >> Well that could certainly matter...
> >> >>
> >> >> --
> >> >> Robert Haas
> >> >> EnterpriseDB: http://www.enterprisedb.com
> >> >> The Enterprise Postgres Company
> >> >>
> >> >
> >>
> >> --
> >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: slow query performance
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Re: slow query performance Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Re: slow query performance Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Re: slow query performance Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 02:54 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 12:44 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:23 ` Re: slow query performance Anj Adu <fotographs@gmail.com>
2010-06-11 13:28 ` Re: slow query performance Kenneth Marshall <ktm@rice.edu>
@ 2010-06-11 13:49 ` Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 16+ messages in thread
From: Matthew Wakeling @ 2010-06-11 13:49 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Anj Adu <fotographs@gmail.com>; Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Andy Colson <andy@squeakycode.net>; pgsql-performance
On Fri, 11 Jun 2010, Kenneth Marshall wrote:
> If you check the archives, you will see that this is not easy
> to do because of the effects of caching.
Indeed. If you were to take the value at completely face value, a modern
hard drive is capable of transferring sequential pages somewhere between
40 and 100 times faster than random pages, depending on the drive.
However, caches tend to favour index scans much more than sequential
scans, so using a value between 40 and 100 would discourage Postgres from
using indexes when they are really the most appropriate option.
Matthew
--
A. Top Posters
> Q. What's the most annoying thing in the world?
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-06-11 13:49 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-03 17:47 slow query performance Anj Adu <fotographs@gmail.com>
2010-06-03 18:43 ` Andy Colson <andy@squeakycode.net>
2010-06-03 20:37 ` Anj Adu <fotographs@gmail.com>
2010-06-10 02:12 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 02:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 03:17 ` Anj Adu <fotographs@gmail.com>
2010-06-10 13:28 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 15:32 ` Anj Adu <fotographs@gmail.com>
2010-06-10 16:42 ` Robert Haas <robertmhaas@gmail.com>
2010-06-10 16:58 ` Anj Adu <fotographs@gmail.com>
2010-06-11 00:49 ` Robert Haas <robertmhaas@gmail.com>
2010-06-11 02:54 ` Anj Adu <fotographs@gmail.com>
2010-06-11 12:44 ` Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:23 ` Anj Adu <fotographs@gmail.com>
2010-06-11 13:28 ` Kenneth Marshall <ktm@rice.edu>
2010-06-11 13:49 ` Matthew Wakeling <matthew@flymine.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox