Thread: [BUGS] BUG #14889: explain analyze is taking much more time than actualexecution
[BUGS] BUG #14889: explain analyze is taking much more time than actualexecution
From
mahmoud.alorfaly@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 14889 Logged by: mahmoud alorfaly Email address: mahmoud.alorfaly@gmail.com PostgreSQL version: 10.0 Operating system: RHel 7 Description: I am testing a simple query performance for an items table of one column and has 4M entries. Running a simple count(*) is finishing in 250ms While Explain Analyze is reaching 4 seconds to complete ! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14889: explain analyze is taking much more time thanactual execution
From
David Rowley
Date:
On 6 November 2017 at 19:50, <mahmoud.alorfaly@gmail.com> wrote: > Running a simple count(*) is finishing in 250ms While Explain Analyze is > reaching 4 seconds to complete ! How about with: EXPLAIN (ANALYZE, TIMING OFF) ? -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14889: explain analyze is taking much more time thanactual execution
From
mahmoud alorfaly
Date:
With EXPLAIN (ANALYZE, TIMING OFF), it is back to normal execution speed.
RegardsOn Mon, Nov 6, 2017 at 10:59 AM, David Rowley <david.rowley@2ndquadrant.com> wrote:
On 6 November 2017 at 19:50, <mahmoud.alorfaly@gmail.com> wrote:
> Running a simple count(*) is finishing in 250ms While Explain Analyze is
> reaching 4 seconds to complete !
How about with: EXPLAIN (ANALYZE, TIMING OFF) ?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: [BUGS] BUG #14889: explain analyze is taking much more time thanactual execution
From
David Rowley
Date:
On 6 November 2017 at 21:04, mahmoud alorfaly <mahmoud.alorfaly@gmail.com> wrote: > With EXPLAIN (ANALYZE, TIMING OFF), it is back to normal execution speed. Then it's just the TIMING overhead that's causing the slowdown. This will vary depending on how long gettimeofday() takes on your system. This is known to be an overhead which is one of the reasons the TIMING OFF option exists. -- David Rowley http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14889: explain analyze is taking much more time than actual execution
From
Tom Lane
Date:
David Rowley <david.rowley@2ndquadrant.com> writes:
> On 6 November 2017 at 21:04, mahmoud alorfaly
> <mahmoud.alorfaly@gmail.com> wrote:
>> With EXPLAIN (ANALYZE, TIMING OFF), it is back to normal execution speed.
> Then it's just the TIMING overhead that's causing the slowdown. This
> will vary depending on how long gettimeofday() takes on your system.
> This is known to be an overhead which is one of the reasons the TIMING
> OFF option exists.
The size of the overhead in this case implies that gettimeofday() is doing
a kernel call, which I wouldn't really have expected on any modern Linux
kernel. What hardware is this on? Is there virtualization involved?
For context, on RHEL6 on run-of-the-mill server iron, I get overheads more
like this:
regression=# explain analyze select i from generate_series(1,10000000) i;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Function
Scanon generate_series i (cost=0.00..10.00 rows=1000 width=4) (actual time=1711.319..3083.864 rows=10000000
loops=1)Planningtime: 0.078 msExecution time: 3636.962 ms
(3 rows)
regression=# explain (analyze, timing off) select i from generate_series(1,10000000) i;
QUERY PLAN
---------------------------------------------------------------------------------------------------------Function Scan
ongenerate_series i (cost=0.00..10.00 rows=1000 width=4) (actual rows=10000000 loops=1)Planning time: 0.077
msExecutiontime: 2870.410 ms
(3 rows)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14889: explain analyze is taking much more time thanactual execution
From
Jeff Janes
Date:
On Mon, Nov 6, 2017 at 12:04 AM, mahmoud alorfaly <mahmoud.alorfaly@gmail.com> wrote:
With EXPLAIN (ANALYZE, TIMING OFF), it is back to normal execution speed.
What do you get out of the command line tool "pg_test_timing"?
Is the server otherwise idle while you run your query, or is it busy with other stuff?
Cheers,
Jeff
Re: [BUGS] BUG #14889: explain analyze is taking much more time thanactual execution
From
Thomas Munro
Date:
On Tue, Nov 7, 2017 at 3:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The size of the overhead in this case implies that gettimeofday() is doing > a kernel call, which I wouldn't really have expected on any modern Linux > kernel. What hardware is this on? Is there virtualization involved? A virtual server with no vDSO, I'll bet. Like this: https://stackoverflow.com/questions/42622427/gettimeofday-not-using-vdso -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs