pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubselect query time and loops problem
4+ messages / 3 participants
[nested] [flat]
* subselect query time and loops problem
@ 2005-04-09 20:21 pankaj naug <pankajnaug@yahoo.com>
2005-04-09 23:26 ` Re: subselect query time and loops problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: pankaj naug @ 2005-04-09 20:21 UTC (permalink / raw)
To: 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 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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: subselect query time and loops problem
2005-04-09 20:21 subselect query time and loops problem pankaj naug <pankajnaug@yahoo.com>
@ 2005-04-09 23:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-04-09 23:26 UTC (permalink / raw)
To: pankaj naug <pankajnaug@yahoo.com>; +Cc: pgsql-sql
pankaj naug <pankajnaug@yahoo.com> writes:
> 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.
Evidently one has been analyzed much more recently than the other,
because the estimated row counts are wildly different.
You didn't say which PG version this is, but I gather that it's pre-7.4,
which means that the performance of IN (SELECT ...) is generally going
to be awful. Either rewrite as a join or update to 7.4 or later.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: subselect query time and loops problem
@ 2005-04-10 14:54 pankaj naug <pankajnaug@yahoo.com>
2005-04-10 17:29 ` Re: subselect query time and loops problem Ragnar Hafstað <gnari@simnet.is>
0 siblings, 1 reply; 4+ messages in thread
From: pankaj naug @ 2005-04-10 14:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Thanks Tom,
I am using version 7.3.4, having problems updating to postgres 8 because of cpanel problems.
regarding,
>Evidently one has been analyzed much more recently than the other,
because the estimated row counts are wildly different.
Both the explain/analyse queries has been run at the same time.
Best regards
Pankaj
---------------------------------
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: subselect query time and loops problem
2005-04-10 14:54 Re: subselect query time and loops problem pankaj naug <pankajnaug@yahoo.com>
@ 2005-04-10 17:29 ` Ragnar Hafstað <gnari@simnet.is>
0 siblings, 0 replies; 4+ messages in thread
From: Ragnar Hafstað @ 2005-04-10 17:29 UTC (permalink / raw)
To: pankaj naug <pankajnaug@yahoo.com>; +Cc: pgsql-sql@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On Sun, 2005-04-10 at 07:54 -0700, pankaj naug wrote:
> [quoting Tom]
> >Evidently one has been analyzed much more recently than the other,
> because the estimated row counts are wildly different.
>
> Both the explain/analyse queries has been run at the same time.
in that case, is the data the same?
if so, what about STATISTICS settings for relevant columns?
just to make things clear, have both databases have been
ANALYZEd or VACUUM ANALYZEd recently ? (in case your
'explain/analyse' only refers to a EXPLAIN ANALYZE)
gnari
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-10 17:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-09 20:21 subselect query time and loops problem pankaj naug <pankajnaug@yahoo.com>
2005-04-09 23:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-10 14:54 Re: subselect query time and loops problem pankaj naug <pankajnaug@yahoo.com>
2005-04-10 17:29 ` Ragnar Hafstað <gnari@simnet.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox