X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 421DD53CC3 for ; Sat, 9 Apr 2005 21:22:07 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39664-05 for ; Sat, 9 Apr 2005 20:22:01 +0000 (GMT) Received: from web30507.mail.mud.yahoo.com (web30507.mail.mud.yahoo.com [68.142.200.120]) by svr1.postgresql.org (Postfix) with SMTP id AF10853D09 for ; Sat, 9 Apr 2005 21:21:52 +0100 (BST) Received: (qmail 71563 invoked by uid 60001); 9 Apr 2005 20:21:54 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=uGvUd3UnXusWiUwyaGvnzav9ha5QMf60TZFTMTbI4pwHh3SXlAHVVZRac9D3xzpI9508YRT68DQQ7GIYlFRcPeOgkBrMgpDUFLWdPMhElvM88q51WI8aF6H757/3ajAAiRBzdIPzM03MqhGnHjyiuXh7wmX1FXD8LamqII9nXSk= ; Message-ID: <20050409202154.71561.qmail@web30507.mail.mud.yahoo.com> Received: from [81.133.216.234] by web30507.mail.mud.yahoo.com via HTTP; Sat, 09 Apr 2005 13:21:54 PDT Date: Sat, 9 Apr 2005 13:21:54 -0700 (PDT) From: pankaj naug Subject: subselect query time and loops problem To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1705556585-1113078114=:71118" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.635 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FORGED_YAHOO_RCVD, HTML_40_50, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200504/135 X-Sequence-Number: 21028 --0-1705556585-1113078114=:71118 Content-Type: text/plain; charset=us-ascii hi, I am using this query. SELECT * FROM guild_properties_buy WHERE agent IN (SELECT agent_id FROM guild_agents WHERE address_region = 'midlands' AND active='on' AND status=0) AND country = 'United Kingdom' AND active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0 when i run this in my development server i get this. Limit (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.97..3632.00 rows=10 loops=1) -> Sort (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.96..3631.98 rows=11 loops=1) Sort Key: price -> Seq Scan on guild_properties_buy (cost=0.00..1680331.29 rows=1 width=541) (actual time=39.39..3556.80 rows=4747 loops=1) Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan)) SubPlan -> Materialize (cost=57.15..57.15 rows=1 width=4) (actual time=0.00..0.05 rows=88 loops=27235) -> Seq Scan on guild_agents (cost=0.00..57.15 rows=1 width=4) (actual time=0.04..1.76 rows=100 loops=1) Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0)) Total runtime: 3633.46 msec when i run this in my hosting server i get this. Limit (cost=847964.41..847964.43 rows=10 width=1036) (actual time=28265.15..28265.19 rows=10 loops=1) -> Sort (cost=847964.41..847999.30 rows=13957 width=1036) (actual time=28265.15..28265.17 rows=11 loops=1) Sort Key: price -> Seq Scan on guild_properties_buy (cost=0.00..832943.58 rows=13957 width=1036) (actual time=6.88..28157.11 rows=4790 loops=1) Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan)) SubPlan -> Seq Scan on guild_agents (cost=0.00..56.15 rows=100 width=4) (actual time=0.01..0.95 rows=87 loops=27173) Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0)) Total runtime: 28269.32 msec when i use my script to replace sub query then both servers run fine. SELECT * FROM guild_properties_buy WHERE agent IN (56259,56397...................... ) AND country = 'United Kingdom' AND active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0 Both servers have same version with same postgresql.conf. But the time taken by both the servers with subselect are way different. the loops and the query time are completely different. both servers have same indexes. Any help will be greately appreciated.. Best Regards Pankaj Naug --------------------------------- Do you Yahoo!? Yahoo! Small Business - Try our new resources site! --0-1705556585-1113078114=:71118 Content-Type: text/html; charset=us-ascii
hi,
 
I am using this query.
 
SELECT * FROM guild_properties_buy WHERE agent IN (SELECT agent_id FROM guild_agents WHERE   address_region = 'midlands'  AND  active='on' AND status=0) AND   country = 'United Kingdom' AND   active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0
 
when i run this in my development server i get this.
 
Limit  (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.97..3632.00 rows=10 loops=1)
  ->  Sort  (cost=1680331.30..1680331.31 rows=1 width=541) (actual time=3631.96..3631.98 rows=11 loops=1)
        Sort Key: price
        ->  Seq Scan on guild_properties_buy  (cost=0.00..1680331.29 rows=1 width=541) (actual time=39.39..3556.80 rows=4747 loops=1)
              Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan))
              SubPlan
                ->  Materialize  (cost=57.15..57.15 rows=1 widt h=4) (actual time=0.00..0.05 rows=88 loops=27235)
                      ->  Seq Scan on guild_agents  (cost=0.00..57.15 rows=1 width=4) (actual time=0.04..1.76 rows=100 loops=1)
                            Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0))
Total runtime: 3633.46 msec
 
when i run this in my hosting server i get this.
 
Limit  (cost=847964.41..847964.43 rows=10 width=1036) (actual time=28265.15..28265.19 rows=10 loops=1)
  ->  Sort  (cost=847964.41..847999.30 rows=13957 width=1036) (actual time=28265.15..28265.17 rows=11 loops=1)
        Sort Key: price
        ->  Seq Scan on guild_properties_buy  (cost=0.00..832943.58 rows=13957 width=1036) (actual time=6.88..28157.11 rows=4790 loops=1)
              Filter: ((country = 'United Kingdom'::character varying) AND (active = 'on'::character varying) AND (status = 0) AND (subplan))
              SubPlan
                ->  Seq Scan on guild_agents  (cost=0.00..56.15 rows=100 width=4) (actual time=0.01..0.95 rows=87 loops=27173)
                      Filter: ((address_region = 'midlands'::character varying) AND (active = 'on'::character varying) AND (status = 0))
Total runtime: 28269.32 msec
 
when i use my script to replace sub query then both servers run fine.
 
SELECT * FROM guild_properties_buy WHERE agent IN (
56259,56397......................
) AND   country = 'United Kingdom' AND   active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0
 
Both servers have same version with same postgresql.conf. But the time taken by both the servers with subselect are way different. the loops and the query time are completely different. both servers have same indexes.
 
Any help will be greately appreciated..
 
Best Regards
Pankaj Naug
 

Do you Yahoo!?
Yahoo! Small Business - Try our new resources site! --0-1705556585-1113078114=:71118--