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