[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Sub-query too slow
From:       Randall Skelton <skelton () brutus ! uwaterloo ! ca>
Date:       2004-03-31 22:41:03
Message-ID: 7DAD9A67-8364-11D8-A5A5-000393C92230 () brutus ! uwaterloo ! ca
[Download RAW message or body]

Can someone please explain how I can make this sub-query faster?  In 
the case below, 'test' is a temporary table but I have tried with test 
being a full, indexed, and 'vacuum analysed' table and it still takes 
more than 130 seconds.  Note that 'test' has very few rows but 
'cal_quat_1' has many rows.

Also, why is it that this takes considerably longer when I omit the 
'order by t' in the sub-select?

Many thanks,
Randall

===
telemetry=> explain analyze select value from cal_quat_1 where 
timestamp in (select t from test order by t);
NOTICE:  QUERY PLAN:

Seq Scan on cal_quat_1  (cost=0.00..7844451.48 rows=2822968 width=8) 
(actual time=68578.99..175922.22 rows=13 loops=1)
   SubPlan
     ->  Sort  (cost=1.37..1.37 rows=13 width=8) (actual time=0.00..0.01 
rows=13 loops=5645935)
           ->  Seq Scan on test  (cost=0.00..1.13 rows=13 width=8) 
(actual time=0.10..0.14 rows=13 loops=1)
Total runtime: 175922.40 msec

EXPLAIN

telemetry=> explain analyze select value from cal_quat_1 where 
timestamp in (select t from test);
NOTICE:  QUERY PLAN:

Seq Scan on cal_quat_1  (cost=0.00..3296489.46 rows=2822968 width=8) 
(actual time=200825.38..511815.02 rows=13 loops=1)
   SubPlan
     ->  Seq Scan on test  (cost=0.00..1.13 rows=13 width=8) (actual 
time=0.01..0.06 rows=13 loops=5645935)
Total runtime: 511815.23 msec

EXPLAIN

telemetry=> explain analyze (select t as timestamp from test);
NOTICE:  QUERY PLAN:

Seq Scan on test  (cost=0.00..1.13 rows=13 width=8) (actual 
time=0.14..0.19 rows=13 loops=1)
Total runtime: 0.30 msec

EXPLAIN

telemetry=> explain analyze (select t as timestamp from test order by 
timestamp);
NOTICE:  QUERY PLAN:

Sort  (cost=1.37..1.37 rows=13 width=8) (actual time=0.47..0.47 rows=13 
loops=1)
   ->  Seq Scan on test  (cost=0.00..1.13 rows=13 width=8) (actual 
time=0.11..0.15 rows=13 loops=1)
Total runtime: 0.58 msec

EXPLAIN
===

Here are the descriptions of 'test' and 'cal_quat_1':

===
telemetry=> create temporary table test (t timestamp unique, q1 float, 
q2 float, q3 float, q4 float);
telemetry=> create index test_idx on test(t);

telemetry=> \d cal_quat_1
                 Table "cal_quat_1"
   Column   |           Type           | Modifiers
-----------+--------------------------+-----------
  timestamp | timestamp with time zone |
  value     | double precision         |
Indexes: cal_quat_1__timestamp
===


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly
[prev in list] [next in list] [prev in thread] [next in thread]