Обсуждение: Sub-query too slow

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
===

On Wed, Mar 31, 2004 at 05:41:03PM -0500, Randall Skelton wrote:
> 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.

Have you tried just using a join? Like:

explain analyze select value from cal_quat_1, test where timestamp = t
order by t;

Also, an index on cal_quat_1.timestamp might be good too...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> This space intentionally left blank

Thanks, that is much better.

On 31 Mar 2004, at 19:14, Martijn van Oosterhout wrote:

> On Wed, Mar 31, 2004 at 05:41:03PM -0500, Randall Skelton wrote:
>> 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.
>
> Have you tried just using a join? Like:
>
> explain analyze select value from cal_quat_1, test where timestamp = t
> order by t;
>
> Also, an index on cal_quat_1.timestamp might be good too...