Postgres Pro
Downloads
Home   >   mailing lists

Thread: 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;

7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;

From
"Rod Taylor"
Date:
Query on 7.0 on Ultra II took over 5 hours.  Query on 7.1 on x86 took
under 10 seconds.  Same data.  Both have been analyzed and vacuum'd
prior to running.   Don't know whether to cheer about speed
improvements, or cry because we can't convert the production system
over to 7.1...

7.0 on Ultra 2

control=# explain SELECT count(*) as "Customers using Extra Services "
control-# FROM customer_info WHERE ( SELECT count(*)
control(# FROM domain_info
control(# , domain_services
control(# , services
control(# WHERE domain_services.service_id = services.service_id
control(# AND domain_info.domain_id = domain_services.domain_id
control(# AND customer_info.acct_id = domain_info.acct_id
control(# AND services.is_package IS FALSE
control(# ) > 0;

NOTICE: QUERY PLAN:
Aggregate (cost=21017481.56..21017481.56 rows=1 width=4)
-> Seq Scan on customer_info (cost=0.00..21017463.54 rows=7206
width=4)
SubPlan
-> Aggregate (cost=972.23..972.23 rows=1 width=24)
-> Hash Join (cost=20.83..972.19 rows=15 width=24)
-> Hash Join (cost=19.33..970.02 rows=21 width=20)
-> Seq Scan on domain_services (cost=0.00..495.97 rows=30297 width=12)
-> Hash (cost=19.28..19.28 rows=18 width=8)
-> Index Scan using domain_info_acct_id_idx on domain_info
(cost=0.00..19.28 rows=18 width=8)
-> Hash (cost=1.44..1.44 rows=25 width=4)
-> Seq Scan on services (cost=0.00..1.44 rows=25 width=4)



7.1 on x86 (FreeBSD)
billing=# explain SELECT count(*) as "Customers using Extra Services "
FROM customer_info WHERE ( SELECT count(*)
billing(# FROM domain_info
billing(# , domain_services
billing(# , services
billing(# WHERE domain_services.service_id = services.service_id
billing(# AND domain_info.domain_id = domain_services.domain_id
billing(# AND customer_info.acct_id = domain_info.acct_id
billing(# AND services.is_package IS FALSE
billing(# ) > 0;

NOTICE: QUERY PLAN:
Aggregate (cost=327708.80..327708.80 rows=1 width=0)
-> Seq Scan on customer_info (cost=0.00..327690.77 rows=7212 width=0)
SubPlan
-> Aggregate (cost=15.11..15.11 rows=1 width=24)
-> Merge Join (cost=14.73..15.10 rows=3 width=24)
-> Sort (cost=12.72..12.72 rows=4 width=20)
-> Nested Loop (cost=0.00..12.67 rows=4 width=20)
-> Index Scan using domain_info_acct_id_idx on domain_info
(cost=0.00..5.45 rows=3 width=8)
-> Index Scan using domain_services_domain_id_idx on domain_services
(cost=0.00..2.05 rows=1 width=12)
-> Sort (cost=2.02..2.02 rows=25 width=4)
-> Seq Scan on services (cost=0.00..1.44 rows=25 width=4)

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
Attachment

Re: 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;

From
Tom Lane
Date:
"Rod Taylor" <rod.taylor@inquent.com> writes:
> Query on 7.0 on Ultra II took over 5 hours.  Query on 7.1 on x86 took
> under 10 seconds.  Same data.

Good ... I guess, because I'm not sure why the difference.  We haven't
done very much in the optimizer since 7.0.  What are the full
declarations of these tables and their indexes?

            regards, tom lane

Re: 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;

From
Joseph Shraibman
Date:
Tom Lane wrote:
>
> "Rod Taylor" <rod.taylor@inquent.com> writes:
> > Query on 7.0 on Ultra II took over 5 hours.  Query on 7.1 on x86 took
> > under 10 seconds.  Same data.
>
> Good ... I guess, because I'm not sure why the difference.  We haven't
> done very much in the optimizer since 7.0.  What are the full
> declarations of these tables and their indexes?
>
>                         regards, tom lane

Sometimes your production and development machines have different data
so behave differently.  With postgres this is more of a problem than in
general because of the planner.

RT: have you tried the same query with the same data on your development
machine with 7.0?

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Re: 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;

From
"Rod Taylor"
Date:
Sorry... Exact same data.  Did a pg_dumpall from one to the other
first, then analyzed.
--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Joseph Shraibman" <jks@selectacast.net>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Rod Taylor" <rod.taylor@inquent.com>;
<pgsql-general@postgresql.org>
Sent: Wednesday, February 28, 2001 6:12 PM
Subject: Re: [GENERAL] 7.0 vs 7.1 running select count(*) FROM table
WHERE (SELECT count(*) ) > 0;


> Tom Lane wrote:
> >
> > "Rod Taylor" <rod.taylor@inquent.com> writes:
> > > Query on 7.0 on Ultra II took over 5 hours.  Query on 7.1 on x86
took
> > > under 10 seconds.  Same data.
> >
> > Good ... I guess, because I'm not sure why the difference.  We
haven't
> > done very much in the optimizer since 7.0.  What are the full
> > declarations of these tables and their indexes?
> >
> >                         regards, tom lane
>
> Sometimes your production and development machines have different
data
> so behave differently.  With postgres this is more of a problem than
in
> general because of the planner.
>
> RT: have you tried the same query with the same data on your
development
> machine with 7.0?
>
> --
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio.  http://www.targabot.com
>