Re: [PERFORM] Simple queries take forever to run

[Michael Guerin]

Stephan Szabo wrote:

On Thu, 28 Aug 2003, Michael Guerin wrote:

 

Stephan Szabo wrote:

   

On Wed, 27 Aug 2003, Michael Guerin wrote:



     

I'm running into some performance problems trying to execute simple
queries.
postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem = 64000
fsync = false
effective_cache_size = 400000
ex. query: select * from x where id in (select id from y);

There's an index on each table for id.  SQL Server takes <1s to return,
postgresql doesn't return at all, neither does explain analyze.
       

IN(subquery) is known to run poorly in 7.3.x and earlier.  7.4 is
generally much better (for reasonably sized subqueries) but in earlier
versions you'll probably want to convert into an EXISTS or join form.


     

Something else seems to be going on, even switching to an exists clause
gives much better but poor performance.
count(*) where exists clause: Postgresql 19s, SQL Server <1s
count(*) where not exists:  23.3s SQL Server 1.5s
   

What does explain analyze show for the two queries?

 

explain analyze  select count(*) from tbltimeseries where exists(select 
uniqid  from tblobjectname where timeseriesid = uniqid);
Aggregate  (cost=5681552.18..5681552.18 rows=1 width=0) (actual 
time=22756.64..22756.64 rows=1 loops=1)
  ->  Seq Scan on tbltimeseries  (cost=0.00..5680051.34 rows=600336 
width=0) (actual time=22.06..21686.78 rows=1200113 loops=1)
        Filter: (NOT (subplan))
        SubPlan
          ->  Index Scan using idx_objectname on tblobjectname  
(cost=0.00..4.70 rows=1 width=4) (actual time=0.01..0.01 rows=0 
loops=1200673)
                Index Cond: ($0 = uniqid)
Total runtime: 22756.83 msec
(7 rows)

fiasco=# explain analyze  select count(*) from tbltimeseries where 
exists(select uniqid  from tblobjectname where timeseriesid = uniqid);
                                                                  QUERY 
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
explain analyze  select count(*) from tbltimeseries where exists(select 
uniqid  from tblobjectname where timeseriesid = uniqid);
Aggregate  (cost=5681552.18..5681552.18 rows=1 width=0) (actual 
time=19558.77..19558.77 rows=1 loops=1)
  ->  Seq Scan on tbltimeseries  (cost=0.00..5680051.34 rows=600336 
width=0) (actual time=0.21..19557.73 rows=560 loops=1)
        Filter: (subplan)
        SubPlan
          ->  Index Scan using idx_objectname on tblobjectname  
(cost=0.00..4.70 rows=1 width=4) (actual time=0.01..0.01 rows=0 
loops=1200673)
                Index Cond: ($0 = uniqid)
Total runtime: 19559.04 msec
(7 rows)





---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings