Hello,
I have noticed that in 9.0.2 EXPLAIN ANALYZE takes considerably longer time to
return than the actual query. I never noticed this in 8.3.
Example:
In 9.0.2 i get
dynacom=# EXPLAIN ANALYZE SELECT count(ms.id) from marinerstates ms,mariner m
where ms.endtime IS NOT NULL AND ms.marinerid=m.id
Achilleas Mantzios writes:
> I have noticed that in 9.0.2 EXPLAIN ANALYZE takes considerably longer time
> to return than the actual query. I never noticed this in 8.3.
Same hardware? What it sounds like is you're running 9.0 on a machine
with slow gettimeofday().
regar
Στις Tuesday 11 January 2011 18:19:11 ο/η Tom Lane έγραψε:
> Achilleas Mantzios writes:
> > I have noticed that in 9.0.2 EXPLAIN ANALYZE takes considerably longer time
> > to return than the actual query. I never noticed this in 8.3.
>
> Same hardware? What it sounds like is you're running 9.0
Achilleas Mantzios writes:
> Regarding gettimeofday (2), i wrote this program :
> #include
> #include
> #include
> int main(int argc,char** argv) {
> struct timeval *tp=calloc(1,sizeof(struct timeval));
> int runna;
> for (runna=0;runna<100;runna++) {
>
Στις Wednesday 12 January 2011 17:07:53 ο/η Tom Lane έγραψε:
> Achilleas Mantzios writes:
> > Regarding gettimeofday (2), i wrote this program :
> > #include
> > #include
> > #include
> > int main(int argc,char** argv) {
> > struct timeval *tp=calloc(1,sizeof(struct timeval));
> >
Achilleas Mantzios writes:
> Óôéò Wednesday 12 January 2011 17:07:53 ï/ç Tom Lane Ýãñáøå:
>> Right offhand I'd wonder whether that was more bound by gettimeofday or
>> by printf. Please try it without printf in the loop.
> Changed that to smth like: micros_total = micros_total + (double) micros;
On Wed, 12 Jan 2011 10:45:20 -0600, Tom Lane wrote:
The short of it is that cheap motherboards tend to provide cheap clock
hardware that takes about a microsecond to read.
I'm not sure how gettimeofday() works, but is there a slight chance this
could be alleviated by choosing a different k
2011/1/12 Achilleas Mantzios :
>
> Changed that to smth like: micros_total = micros_total + (double) micros;
> instead of the printf to beat any compiler optimization, and still linux runs
> at light speed:
> FBSD_TEST : user 0.089s, sys 1.4s
> FBSD_DEV : user 0.183s, sys 3.8s
> LINUX_PROD : user
Στις Wednesday 12 January 2011 21:17:00 ο/η Alex Hunsaker έγραψε:
> 2011/1/12 Achilleas Mantzios :
>
> >
> > Changed that to smth like: micros_total = micros_total + (double) micros;
> > instead of the printf to beat any compiler optimization, and still linux
> > runs at light speed:
> > FBSD_TES
Στις Wednesday 12 January 2011 19:17:28 ο/η Mark Felder έγραψε:
> On Wed, 12 Jan 2011 10:45:20 -0600, Tom Lane wrote:
> >
> > The short of it is that cheap motherboards tend to provide cheap clock
> > hardware that takes about a microsecond to read.
>
> I'm not sure how gettimeofday() works, but
Στις Wednesday 12 January 2011 18:45:20 ο/η Tom Lane έγραψε:
> Achilleas Mantzios writes:
> > Στις Wednesday 12 January 2011 17:07:53 ο/η Tom Lane έγραψε:
> >> Right offhand I'd wonder whether that was more bound by gettimeofday or
> >> by printf. Please try it without printf in the loop.
>
> >
Στις Thursday 13 January 2011 14:13:21 ο/η Achilleas Mantzios έγραψε:
> SELECT distinct
> m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surname from
> marinerstates ms,vessels vsl,mariner m
> where m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and
> coalesce(ms.endtime,n
Στις Wednesday 12 January 2011 18:45:20 ο/η Tom Lane έγραψε:
> Achilleas Mantzios writes:
> > Στις Wednesday 12 January 2011 17:07:53 ο/η Tom Lane έγραψε:
> >> Right offhand I'd wonder whether that was more bound by gettimeofday or
> >> by printf. Please try it without printf in the loop.
>
> >
Στις Thursday 13 January 2011 16:13:23 ο/η Achilleas Mantzios έγραψε:
>
> Next step, if i am lucky tonight, i'll set up a new linux box with 9.0.2 and
> load
> the same small (coming from FBSD_DEV) database.
>
> So my problem was twofold : a) EXPLAIN ANALYZE performance, b) the particular
> sl
Achilleas Mantzios wrote:
> and NOT EXISTS
>(SELECT distinct mold.id from marinerstates
Does that do better if you leave out the DISTINCT keyword? Maybe
use * instead of the column name?
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your
Στις Friday 14 January 2011 16:41:18 γράψατε:
> Achilleas Mantzios wrote:
>
> > and NOT EXISTS
> >(SELECT distinct mold.id from marinerstates
>
> Does that do better if you leave out the DISTINCT keyword? Maybe
> use * instead of the column name?
Its already really fast, it doesn't matt
Στις Friday 14 January 2011 16:41:18 γράψατε:
> Achilleas Mantzios wrote:
>
> > and NOT EXISTS
> >(SELECT distinct mold.id from marinerstates
>
> Does that do better if you leave out the DISTINCT keyword? Maybe
> use * instead of the column name?
Its already really fast, it doesn't matt
Στις Friday 14 January 2011 16:41:18 γράψατε:
> Achilleas Mantzios wrote:
>
> > and NOT EXISTS
> >(SELECT distinct mold.id from marinerstates
>
> Does that do better if you leave out the DISTINCT keyword? Maybe
> use * instead of the column name?
Its already really fast, it doesn't matt
Achilleas Mantzios wrote:
> The other form (NOT IN) is the problem (in the case of
> postgresql-9.0.2). (in 8.3 both queries are fast)
So running EXPLAIN ANALYZE for this plan on the 9.0 version checks
the time in more places than the 8.3 version. For a plan that's an
order of magnitude slow
Στις Friday 14 January 2011 22:06:56 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios wrote:
>
> > The other form (NOT IN) is the problem (in the case of
> > postgresql-9.0.2). (in 8.3 both queries are fast)
>
> So running EXPLAIN ANALYZE for this plan on the 9.0 version checks
> the time in m
Στις Friday 14 January 2011 22:06:56 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios wrote:
>
> > The other form (NOT IN) is the problem (in the case of
> > postgresql-9.0.2). (in 8.3 both queries are fast)
>
> So running EXPLAIN ANALYZE for this plan on the 9.0 version checks
> the time in m
Achilleas Mantzios wrote:
> What i am asking is ways to understand what is going on, and what
> to expect when i will have to actually perform the production
> upgrade to 9.0.2
Sorry to have focused on the wrong issues from your latest post.
Here's what I suggest to try to get you the best in
Στις Friday 14 January 2011 23:20:25 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios wrote:
>
> > What i am asking is ways to understand what is going on, and what
> > to expect when i will have to actually perform the production
> > upgrade to 9.0.2
>
> Sorry to have focused on the wrong issu
Achilleas Mantzios wrote:
> I agree about the subject line. But whouldn't -bugs be a more
> apropriate place than -performance?
Is it returning incorrect results, or is it not running as fast as
you would like?
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To mak
Στις Saturday 15 January 2011 00:35:44 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios wrote:
>
> > I agree about the subject line. But whouldn't -bugs be a more
> > apropriate place than -performance?
>
> Is it returning incorrect results, or is it not running as fast as
> you would like?
alr
25 matches
Mail list logo
