pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh Planning Time
4+ messages / 4 participants
[nested] [flat]
* High Planning Time
@ 2016-01-22 00:30 Phil S <pjsanders@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Phil S @ 2016-01-22 00:30 UTC (permalink / raw)
To: pgsql-performance
I am running Postgresql on a Windows Server 2008 server. I have noticed
that queries have very high planning times now and then. Planning times go
down for the same query immediately after the query runs the first time,
but then go up again after if the query is not re-run for 5 minutes or so.
I am not able to find any specific information in the documentation that
would explain the issue or explains how to address it, so am asking for
advice here.
Here is an example.
explain analyze
select * from message
limit 1
"Limit (cost=0.00..0.44 rows=1 width=1517) (actual time=0.009..0.009
rows=1 loops=1)"
" -> Seq Scan on message (cost=0.00..28205.48 rows=64448 width=1517)
(actual time=0.007..0.007 rows=1 loops=1)"
"Planning time: 3667.361 ms"
"Execution time: 1.652 ms"
As you can see the query is simple and does not justify 3 seconds of
planning time. It would appear that there is an issue with my configuration
but I am not able to find anything that looks out of sorts in the query
planning configuration variables. Any advice about what I should be looking
for to fix this would be appreciated.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: High Planning Time
@ 2016-01-22 07:59 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Phil S <pjsanders@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Albe Laurenz @ 2016-01-22 07:59 UTC (permalink / raw)
To: 'Phil S *EXTERN*' <pjsanders@gmail.com>; pgsql-performance
Phil S wrote:
> I am running Postgresql on a Windows Server 2008 server. I have noticed that queries have very high
> planning times now and then. Planning times go down for the same query immediately after the query
> runs the first time, but then go up again after if the query is not re-run for 5 minutes or so.
>
> I am not able to find any specific information in the documentation that would explain the issue or
> explains how to address it, so am asking for advice here.
>
> Here is an example.
>
> explain analyze
> select * from message
> limit 1
>
> "Limit (cost=0.00..0.44 rows=1 width=1517) (actual time=0.009..0.009 rows=1 loops=1)"
> " -> Seq Scan on message (cost=0.00..28205.48 rows=64448 width=1517) (actual time=0.007..0.007
> rows=1 loops=1)"
> "Planning time: 3667.361 ms"
> "Execution time: 1.652 ms"
>
> As you can see the query is simple and does not justify 3 seconds of planning time. It would appear
> that there is an issue with my configuration but I am not able to find anything that looks out of
> sorts in the query planning configuration variables. Any advice about what I should be looking for to
> fix this would be appreciated.
This is odd.
Could you profile the backend during such a statement to see where the time is spent?
Yours,
Laurenz Albe
--
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] 4+ messages in thread
* Re: High Planning Time
@ 2016-01-22 14:11 Vitalii Tymchyshyn <vit@tym.im>
parent: Phil S <pjsanders@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Vitalii Tymchyshyn @ 2016-01-22 14:11 UTC (permalink / raw)
To: Phil S <pjsanders@gmail.com>; pgsql-performance
It can be because of catalog stats file leak. I had it on some older
PostgreSQL version when tests were rolling back schema changes often. The
file grew to some enormous size and as soon as it got out of cache - it
would be very slow to access catalog. I had just to kill the file.
Best regards, Vitalii Tymchyshyn
Чт, 21 січ. 2016 19:30 Phil S <pjsanders@gmail.com> пише:
> I am running Postgresql on a Windows Server 2008 server. I have noticed
> that queries have very high planning times now and then. Planning times go
> down for the same query immediately after the query runs the first time,
> but then go up again after if the query is not re-run for 5 minutes or so.
>
> I am not able to find any specific information in the documentation that
> would explain the issue or explains how to address it, so am asking for
> advice here.
>
> Here is an example.
>
> explain analyze
> select * from message
> limit 1
>
> "Limit (cost=0.00..0.44 rows=1 width=1517) (actual time=0.009..0.009
> rows=1 loops=1)"
> " -> Seq Scan on message (cost=0.00..28205.48 rows=64448 width=1517)
> (actual time=0.007..0.007 rows=1 loops=1)"
> "Planning time: 3667.361 ms"
> "Execution time: 1.652 ms"
>
> As you can see the query is simple and does not justify 3 seconds of
> planning time. It would appear that there is an issue with my configuration
> but I am not able to find anything that looks out of sorts in the query
> planning configuration variables. Any advice about what I should be looking
> for to fix this would be appreciated.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: High Planning Time
@ 2016-01-22 14:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-01-22 14:35 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: 'Phil S *EXTERN*' <pjsanders@gmail.com>; pgsql-performance
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Phil S wrote:
>> explain analyze
>> select * from message
>> limit 1
>>
>> "Limit (cost=0.00..0.44 rows=1 width=1517) (actual time=0.009..0.009 rows=1 loops=1)"
>> " -> Seq Scan on message (cost=0.00..28205.48 rows=64448 width=1517) (actual time=0.007..0.007
>> rows=1 loops=1)"
>> "Planning time: 3667.361 ms"
>> "Execution time: 1.652 ms"
>>
>> As you can see the query is simple and does not justify 3 seconds of planning time. It would appear
>> that there is an issue with my configuration but I am not able to find anything that looks out of
>> sorts in the query planning configuration variables. Any advice about what I should be looking for to
>> fix this would be appreciated.
> This is odd.
> Could you profile the backend during such a statement to see where the time is spent?
I'm wondering about locks. Perhaps turning on log_lock_waits would
yield useful info.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-01-22 14:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-22 00:30 High Planning Time Phil S <pjsanders@gmail.com>
2016-01-22 07:59 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2016-01-22 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-01-22 14:11 ` Vitalii Tymchyshyn <vit@tym.im>
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