Thread: Glacially slow nested SELECT

Hello all,

I have a query that is extraordinarily slow but I don't know why. It
crosses a many-to-many join table and looks like this:

SELECT * FROM spview WHERE id IN (SELECT signal_profile_id FROM
track_to_signal_profile WHERE track_id = 19510985);

The three tables are:

track <-> track_to_signal_profile <-> signal_profile (where spview is
a view with rows from that table only)

I ran the query with "explain analyse" and left it over the weekend
and it didn't finish. (The "signal_profile" table has on order
350,000,000 rows.)

Now, this is the part I don't understand. The subquery finishes
instantly:

driftdb=# EXPLAIN ANALYSE SELECT signal_profile_id FROM
track_to_signal_profile WHERE track_id = 19510985;
Index Scan using unique_sp_and_track on track_to_signal_profile
(cost=0.00..11892.92 rows=5014 width=8) (actual time=0.018..0.023
rows=3 loops=1)
    Index Cond: (track_id = 19510985)
Total runtime: 0.058 ms
(3 rows)

If I take the result and do the rest by hand, it's also instant:

driftdb=# EXPLAIN ANALYSE SELECT * FROM spview WHERE id IN
(1705521616, 1705521681, 1705521693);
Subquery Scan spview  (cost=63.62..63.66 rows=3 width=292) (actual
time=0.095..0.109 rows=3 loops=1)
    ->  Sort  (cost=63.62..63.63 rows=3 width=96) (actual
time=0.091..0.096 rows=3 loops=1)
          Sort Key: signal_profile."trigger", signal_profile.mwpc,
signal_profile.readout, signal_profile.signal_profile_index
          ->  Bitmap Heap Scan on signal_profile  (cost=51.45..63.60
rows=3 width=96) (actual time=0.049..0.068 rows=3 loops=1)
                Recheck Cond: (id = ANY
('{1705521616,1705521681,1705521693}'::integer[]))
                ->  Bitmap Index Scan on signal_profile_pkey
(cost=0.00..51.45 rows=3 width=0) (actual time=0.027..0.027 rows=3
loops=1)
                      Index Cond: (id = ANY
('{1705521616,1705521681,1705521693}'::integer[]))
Total runtime: 0.190 ms
(8 rows)

So the data can be found instantly, but when I put the two queries in
one line it fails. Is there a type conversion/confusion somewhere?

I would appreciate any suggestions!

Cheers,

Demitri

thatsanicehatyouhave@mac.com writes:
> I have a query that is extraordinarily slow but I don't know why.

It looks like spview is a view with an embedded ORDER BY?  IIRC that
prevents any meaningful optimization of joins to it --- and
WHERE-IN-sub-SELECT is a kind of join.

            regards, tom lane

On 15 Jan 2007, at 16:21, Tom Lane wrote:

> thatsanicehatyouhave@mac.com writes:
>> I have a query that is extraordinarily slow but I don't know why.
>
> It looks like spview is a view with an embedded ORDER BY?  IIRC that
> prevents any meaningful optimization of joins to it --- and
> WHERE-IN-sub-SELECT is a kind of join.

Thanks for the pointer Tom; removing the ORDER BY from the view did
the trick. Is there a reason that the sorting can't be done after the
selection? I suppose I can solve this with a function.

Cheers,

Demitri

Demitri Muna <thatsanicehatyouhave@mac.com> writes:
> On 15 Jan 2007, at 16:21, Tom Lane wrote:
>> It looks like spview is a view with an embedded ORDER BY?  IIRC that
>> prevents any meaningful optimization of joins to it --- and
>> WHERE-IN-sub-SELECT is a kind of join.

> Thanks for the pointer Tom; removing the ORDER BY from the view did
> the trick. Is there a reason that the sorting can't be done after the
> selection?

Well, it'd require major revisions to the planner, and it'd break
various queries that depend on the planner honoring sub-select ORDER BY
(people use that to guarantee the order in which values are fed to
custom aggregates, for instance).

            regards, tom lane