Hi,
After my first mail, I found a better testcase (well it's a about the
same, but you have a better look to compare).
See attachment prob-query.sql
The ORDER BY in the FROM clause uses the index.
The last ORDER BY does not use the index.
They should be the same...
The query plans are identical to the plans in my previous mail.
With kind regards,
Mathieu
P.S.: i just noted that i forgot to attach to previous mail, i'll
attach everything now.
SELECT v.id , v.title, v.artist, v.music, v.lyrics, v.year
FROM
( SELECT
href('song', r.id, r.title) AS title, r.title AS search_title,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 0)
) AS artist,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 0)
) AS search_artist,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 1)
) AS music,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 1)
) AS search_music,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 2)
) AS lyrics,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 2)
) AS search_lyrics,
r.year AS year,
r.year AS search_year,
r.id AS id
FROM song r
ORDER BY search_title
) AS v
ORDER BY v.search_title
LIMIT 20 OFFSET 0 ;
coverdb=# explain analyze SELECT id , title, artist, music, lyrics, year FROM
search_song WHERE search_title IS NOT NULL LIMIT 20 OFFSET 0 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.18 rows=20 width=28) (actual time=0.47..9.57 rows=20 loops=1)
-> Subquery Scan search_song (cost=0.00..390.11 rows=6593 width=28) (actual
time=0.47..9.54 rows=21 loops=1)
-> Index Scan using song_title_idx on song r (cost=0.00..390.11 rows=6593
width=28) (actual time=0.47..9.47 rows=21 loops=1)
Filter: (title IS NOT NULL)
SubPlan
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.24..0.24 rows=1 loops=21)
-> Nested Loop (cost=0.00..8.13 rows=2 width=25) (actual
time=0.03..0.03 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.02 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..8.13 rows=2 width=25) (actual
time=0.02..0.03 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.74..6.74 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.74 rows=1 width=25) (actual
time=0.02..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.74..6.74 rows=1 width=25) (actual
time=0.03..0.03 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.74 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.03..0.03 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
Total runtime: 9.98 msec
(48 rows)
coverdb=# explain analyze SELECT id , title, artist, music, lyrics, year FROM
search_song WHERE search_title IS NOT NULL ORDER BY search_title LIMIT 20 OFFSET 0 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=808.33..808.38 rows=20 width=28) (actual time=2276.68..2276.70 rows=20
loops=1)
-> Sort (cost=808.33..824.81 rows=6593 width=28) (actual time=2276.67..2276.68
rows=21 loops=1)
Sort Key: search_title
-> Subquery Scan search_song (cost=0.00..390.11 rows=6593 width=28) (actual
time=0.47..2231.64 rows=6380 loops=1)
-> Index Scan using song_title_idx on song r (cost=0.00..390.11
rows=6593 width=28) (actual time=0.47..2202.12 rows=6380 loops=1)
Filter: (title IS NOT NULL)
SubPlan
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.07..0.07 rows=1 loops=6380)
-> Nested Loop (cost=0.00..8.13 rows=2 width=25)
(actual time=0.04..0.04 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.02..0.02 rows=1 loops=6341)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.05..0.05 rows=1 loops=6380)
-> Nested Loop (cost=0.00..8.13 rows=2 width=25)
(actual time=0.03..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.02..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=6341)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.74..6.74 rows=1 width=25) (actual
time=0.06..0.06 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.74 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.02 rows=1 loops=4497)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.74..6.74 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.74 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4497)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.05..0.05 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.02 rows=1 loops=4463)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4463)
Index Cond: ("outer".artist_id = a.id)
Total runtime: 2278.43 msec
(50 rows)
coverdb=# explain analyze SELECT id , title, artist, music, lyrics, year FROM
search_song WHERE search_title IS NOT NULL LIMIT 20 OFFSET 0 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.41 rows=20 width=28) (actual time=1.52..6.54 rows=20 loops=1)
-> Subquery Scan search_song (cost=0.00..134.93 rows=6593 width=28) (actual
time=1.52..6.53 rows=21 loops=1)
-> Seq Scan on song r (cost=0.00..134.93 rows=6593 width=28) (actual
time=1.51..6.45 rows=21 loops=1)
Filter: (title IS NOT NULL)
SubPlan
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.07..0.07 rows=1 loops=21)
-> Nested Loop (cost=0.00..8.14 rows=2 width=25) (actual
time=0.02..0.03 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=8.14..8.14 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..8.14 rows=2 width=25) (actual
time=0.02..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.35..6.35 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.35 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.35..6.35 rows=1 width=25) (actual
time=0.03..0.03 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.35 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=15)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.45..6.45 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.45 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=14)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.45..6.45 rows=1 width=25) (actual
time=0.03..0.03 rows=1 loops=21)
-> Nested Loop (cost=0.00..6.45 rows=1 width=25) (actual
time=0.01..0.02 rows=1 loops=21)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=21)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=14)
Index Cond: ("outer".artist_id = a.id)
Total runtime: 7.23 msec
(48 rows)
coverdb=# explain analyze SELECT id , title, artist, music, lyrics, year FROM
search_song WHERE search_title IS NOT NULL ORDER BY search_title LIMIT 20 OFFSET 0 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=553.15..553.20 rows=20 width=28) (actual time=2227.78..2227.80 rows=20
loops=1)
-> Sort (cost=553.15..569.63 rows=6593 width=28) (actual time=2227.78..2227.78
rows=21 loops=1)
Sort Key: search_title
-> Subquery Scan search_song (cost=0.00..134.93 rows=6593 width=28) (actual
time=1.09..2153.71 rows=6380 loops=1)
-> Seq Scan on song r (cost=0.00..134.93 rows=6593 width=28) (actual
time=1.09..2125.37 rows=6380 loops=1)
Filter: (title IS NOT NULL)
SubPlan
-> Aggregate (cost=8.10..8.10 rows=1 width=25) (actual
time=0.06..0.06 rows=1 loops=6380)
-> Nested Loop (cost=0.00..8.09 rows=2 width=25)
(actual time=0.03..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.01 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=6341)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=8.10..8.10 rows=1 width=25) (actual
time=0.05..0.05 rows=1 loops=6380)
-> Nested Loop (cost=0.00..8.09 rows=2 width=25)
(actual time=0.03..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=2 width=4) (actual time=0.01..0.01 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 0)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=6341)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.05..0.05 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4497)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.62..6.62 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.62 rows=1 width=25)
(actual time=0.02..0.03 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 1)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4497)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.76..6.76 rows=1 width=25) (actual
time=0.05..0.05 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.76 rows=1 width=25)
(actual time=0.01..0.02 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4463)
Index Cond: ("outer".artist_id = a.id)
-> Aggregate (cost=6.76..6.76 rows=1 width=25) (actual
time=0.04..0.04 rows=1 loops=6380)
-> Nested Loop (cost=0.00..6.76 rows=1 width=25)
(actual time=0.01..0.02 rows=1 loops=6380)
-> Index Scan using credit_song_idx on credit c
(cost=0.00..3.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=6380)
Index Cond: (song_id = $0)
Filter: (type_id = 2)
-> Index Scan using artist_pkey on artist a
(cost=0.00..3.02 rows=1 width=21) (actual time=0.01..0.01 rows=1 loops=4463)
Index Cond: ("outer".artist_id = a.id)
Total runtime: 2229.73 msec
(50 rows)
-- with sorting
SELECT id , title, artist, music, lyrics, year FROM search_song WHERE search_title IS
NOT NULL ORDER BY search_title LIMIT 20 OFFSET 0 ;
-- without sorting
SELECT id , title, artist, music, lyrics, year FROM search_song WHERE search_title IS
NOT NULL LIMIT 20 OFFSET 0 ;
CREATE VIEW search_song AS
SELECT
href('song', r.id, r.title) AS title, r.title AS search_title,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 0)
) AS artist,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 0)
) AS search_artist,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 1)
) AS music,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 1)
) AS search_music,
( SELECT br(href('artist', a.id, a.name))
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 2)
) AS lyrics,
( SELECT comma(a.name)
FROM credit c
JOIN artist a ON (c.artist_id = a.id)
WHERE (c.song_id = r.id AND c.type_id = 2)
) AS search_lyrics,
r.year AS year,
r.year AS search_year,
--href('album', alb.id, alb.title) AS album,
--alb.title AS search_album,
r.id AS id
FROM song r
--LEFT JOIN album alb ON (r.album_id = alb.id)
-- Uncomment next line for 'presorted'
-- ORDER BY search_title
;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
