Re: PostgreSQL caching
От | Jochem van Dieten |
---|---|
Тема | Re: PostgreSQL caching |
Дата | |
Msg-id | 40B36848.1090403@oli.tudelft.nl обсуждение исходный текст |
Ответ на | Re: PostgreSQL caching (Vitaly Belman <vitalib@012.net.il>) |
Ответы |
Re: PostgreSQL caching
(Vitaly Belman <vitalib@012.net.il>)
|
Список | pgsql-performance |
Vitaly Belman wrote: > > If you'll be so kind though, I'd be glad if you could spot anything to > speed up in this query. Here's the query and its plan that happens > without any caching: > > ------------------------------------------------------------------------------------------------------------- > QUERY > ----- > SELECT bv_books. * , > vote_avg, > vote_count > FROM bv_bookgenres, > bv_books > WHERE bv_books.book_id = bv_bookgenres.book_id AND > bv_bookgenres.genre_id = 5830 > ORDER BY vote_avg DESC LIMIT 10 OFFSET 0; > > QUERY PLAN > ---------- > Limit (cost=2337.41..2337.43 rows=10 width=76) (actual time=7875.000..7875.000 rows=10 loops=1) > -> Sort (cost=2337.41..2337.94 rows=214 width=76) (actual time=7875.000..7875.000 rows=10 loops=1) > Sort Key: bv_books.vote_avg > -> Nested Loop (cost=0.00..2329.13 rows=214 width=76) (actual time=16.000..7844.000 rows=1993 loops=1) > -> Index Scan using i_bookgenres_genre_id on bv_bookgenres (cost=0.00..1681.54 rows=214 width=4) (actualtime=16.000..3585.000 rows=1993 loops=1) > Index Cond: (genre_id = 5830) > -> Index Scan using bv_books_pkey on bv_books (cost=0.00..3.01 rows=1 width=76) (actual time=2.137..2.137rows=1 loops=1993) > Index Cond: (bv_books.book_id = "outer".book_id) > Total runtime: 7875.000 ms Presuming that vote_avg is a field in the table bv_bookgenres, try a composite index on genre_id and vote_avg and then see if you can use the limit clause to reduce the number of loop iterations from 1993 to 10. CREATE INDEX test_idx ON bv_bookgenres (genre_id, vote_avg); The following query tries to force that execution lan and, presuming there is a foreign key relation between bv_books.book_id AND bv_bookgenres.book_id, I expect it will give the same results, but be carefull with NULL's: SELECT bv_books. * , vote_avg, vote_count FROM ( SELECT bg.* FROM bv_bookgenres bg WHERE bg.genre_id = 5830 ORDER BY bg.vote_avg DESC LIMIT 10 ) bv_bookgenres, bv_books WHERE bv_books.book_id = bv_bookgenres.book_id ORDER BY vote_avg DESC LIMIT 10; Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje
В списке pgsql-performance по дате отправления: