prepared query performs much worse than regular query
От | Richard Yen |
---|---|
Тема | prepared query performs much worse than regular query |
Дата | |
Msg-id | 0E6DA13D-D053-4CB8-A301-DC3607BBF146@richyen.com обсуждение исходный текст |
Ответы |
Re: prepared query performs much worse than regular query
(Rosser Schwarz <rosser.schwarz@gmail.com>)
Re: prepared query performs much worse than regular query (Matthew Wakeling <matthew@flymine.org>) |
Список | pgsql-performance |
Hi everyone, I use DBD::Pg to interface with our 8.4.2 database, but for a particular query, performance is horrible. I'm assuming thatthe behavior of $dbh->prepare is as if I did PREPARE foo AS (query), so I did an explain analyze in the commandline: > db_alpha=# prepare foo6 as (SELECT me.id, me.assignment, me.title, me.x_firstname, me.x_lastname, me.owner, me.node, me.grade,me.folder, me.word_count, me.char_length, me.char_count, me.page_count FROM submissions me WHERE ( ( owner = $1AND me.assignment = $2 ) )); > PREPARE > db_alpha=# explain analyze execute foo6('-1', '8996557'); > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------------- > Bitmap Heap Scan on submissions me (cost=38.84..42.85 rows=1 width=70) (actual time=346567.665..346567.665 rows=0 loops=1) > Recheck Cond: ((assignment = $2) AND (owner = $1)) > -> BitmapAnd (cost=38.84..38.84 rows=1 width=0) (actual time=346567.642..346567.642 rows=0 loops=1) > -> Bitmap Index Scan on submissions_assignment_idx (cost=0.00..19.27 rows=177 width=0) (actual time=0.038..0.038rows=2 loops=1) > Index Cond: (assignment = $2) > -> Bitmap Index Scan on submissions_owner_idx (cost=0.00..19.32 rows=184 width=0) (actual time=346566.501..346566.501rows=28977245 loops=1) > Index Cond: (owner = $1) > Total runtime: 346567.757 ms > (8 rows) Now, if I run it without preparing it--just run it directly in the commandline--I get this plan: > db_alpha=# explain analyze SELECT me.id, me.assignment, me.title, me.x_firstname, me.x_lastname, me.owner, me.node, me.grade,me.folder, me.word_count, me.char_length, me.char_count, me.page_count FROM submissions me WHERE ( ( owner = -1AND me.assignment = 8996557 ) ) > db_alpha-# ; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------- > Index Scan using submissions_assignment_idx on submissions me (cost=0.00..549.15 rows=36 width=70) (actual time=0.021..0.021rows=0 loops=1) > Index Cond: (assignment = 8996557) > Filter: (owner = (-1)) > Total runtime: 0.042 ms > (4 rows) submissions has ~124 million rows, and owner -1 is a placeholder in my database, to fulfill a foreign key requirement. Itried REINDEXing submissions_owner_idx and performing a VACUUM ANALYZE on the submissions table, but nothing seems to makea difference for this query. One other thing to note is that if I use any other value for the owner column, it comesback really fast (< 0.04 ms). Any ideas why the query planner chooses a different query plan when using prepared statements? --Richard
В списке pgsql-performance по дате отправления:
Предыдущее
От: Stephen FrostДата:
Сообщение: Re: Optimize date query for large child tables: GiST or GIN?
Следующее
От: Rosser SchwarzДата:
Сообщение: Re: prepared query performs much worse than regular query