subselect query time and loops problem - Mailing list pgsql-sql

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 ( ) AND   country = 'United Kingdom' AND   active='on' AND status='0' ORDER BY price DESC LIMIT 10 OFFSET 0

 

 

Any help will be greately appreciated..

 

Best Regards

Pankaj Naug