pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedplaner chooses very bad plan
8+ messages / 5 participants
[nested] [flat]
* planer chooses very bad plan
@ 2010-04-11 21:12 Corin <wakathane@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Corin @ 2010-04-11 21:12 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm having a query where the planer chooses a very bad plan.
explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
ORDER BY id DESC LIMIT 10 OFFSET 0
"Limit (cost=0.00..1557.67 rows=10 width=78) (actual
time=0.096..2750.058 rows=5 loops=1)"
" -> Index Scan Backward using telegrams_pkey on telegrams
(cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052
rows=5 loops=1)"
" Filter: (((recipient_id = 508933) AND (NOT recipient_deleted))
OR ((user_id = 508933) AND (NOT user_deleted)))"
"Total runtime: 2750.124 ms"
When I force the planer not use do index scans, the plans looks MUCH
better (10.000x faster):
set enable_indexscan = false;
explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
ORDER BY id DESC LIMIT 10 OFFSET 0
"Limit (cost=2547.16..2547.16 rows=10 width=78) (actual
time=0.179..0.185 rows=5 loops=1)"
" -> Sort (cost=2547.16..2547.41 rows=1005 width=78) (actual
time=0.177..0.178 rows=5 loops=1)"
" Sort Key: id"
" Sort Method: quicksort Memory: 26kB"
" -> Bitmap Heap Scan on telegrams (cost=17.39..2544.98
rows=1005 width=78) (actual time=0.124..0.158 rows=5 loops=1)"
" Recheck Cond: ((recipient_id = 508933) OR (user_id =
508933))"
" Filter: (((recipient_id = 508933) AND (NOT
recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))"
" -> BitmapOr (cost=17.39..17.39 rows=1085 width=0)
(actual time=0.104..0.104 rows=0 loops=1)"
" -> Bitmap Index Scan on telegrams_recipient
(cost=0.00..8.67 rows=536 width=0) (actual time=0.033..0.033 rows=1
loops=1)"
" Index Cond: (recipient_id = 508933)"
" -> Bitmap Index Scan on telegrams_user
(cost=0.00..8.67 rows=549 width=0) (actual time=0.069..0.069 rows=4
loops=1)"
" Index Cond: (user_id = 508933)"
"Total runtime: 0.276 ms"
The table contains several millions records and it's just be
reindexed/analyzed.
Are there any parameters I can tune so that pgsql itself chooses the
best plan? :)
# - Memory -
shared_buffers = 256MB
temp_buffers = 32MB
work_mem = 4MB
maintenance_work_mem = 32MB
# - Planner Cost Constants -
seq_page_cost = 1.0
random_page_cost = 2.5
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.00025
effective_cache_size = 20GB
# - Genetic Query Optimizer -
geqo = on
Thanks,
Corin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 21:18 Scott Marlowe <scott.marlowe@gmail.com>
parent: Corin <wakathane@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2010-04-11 21:18 UTC (permalink / raw)
To: Corin <wakathane@gmail.com>; +Cc: pgsql-performance
On Sun, Apr 11, 2010 at 3:12 PM, Corin <wakathane@gmail.com> wrote:
> Hi,
>
> I'm having a query where the planer chooses a very bad plan.
In both instances your number of rows estimated is WAY higher than the
actual number of rows returned. Perhaps if you increased
default_statistics_target to 100, 200, 500 etc. re-analyzed, and then
reun explain analyze again.
Also increasing work_mem might encourage the bitmap index scans to occur.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 21:22 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 8+ messages in thread
From: Luke Lonergan @ 2010-04-11 21:22 UTC (permalink / raw)
To: wakathane@gmail.com <wakathane@gmail.com>; pgsql-performance
Try random_page_cost=100
- Luke
----- Original Message -----
From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Sent: Sun Apr 11 14:12:30 2010
Subject: [PERFORM] planer chooses very bad plan
Hi,
I'm having a query where the planer chooses a very bad plan.
explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
ORDER BY id DESC LIMIT 10 OFFSET 0
"Limit (cost=0.00..1557.67 rows=10 width=78) (actual
time=0.096..2750.058 rows=5 loops=1)"
" -> Index Scan Backward using telegrams_pkey on telegrams
(cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052
rows=5 loops=1)"
" Filter: (((recipient_id = 508933) AND (NOT recipient_deleted))
OR ((user_id = 508933) AND (NOT user_deleted)))"
"Total runtime: 2750.124 ms"
When I force the planer not use do index scans, the plans looks MUCH
better (10.000x faster):
set enable_indexscan = false;
explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
ORDER BY id DESC LIMIT 10 OFFSET 0
"Limit (cost=2547.16..2547.16 rows=10 width=78) (actual
time=0.179..0.185 rows=5 loops=1)"
" -> Sort (cost=2547.16..2547.41 rows=1005 width=78) (actual
time=0.177..0.178 rows=5 loops=1)"
" Sort Key: id"
" Sort Method: quicksort Memory: 26kB"
" -> Bitmap Heap Scan on telegrams (cost=17.39..2544.98
rows=1005 width=78) (actual time=0.124..0.158 rows=5 loops=1)"
" Recheck Cond: ((recipient_id = 508933) OR (user_id =
508933))"
" Filter: (((recipient_id = 508933) AND (NOT
recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))"
" -> BitmapOr (cost=17.39..17.39 rows=1085 width=0)
(actual time=0.104..0.104 rows=0 loops=1)"
" -> Bitmap Index Scan on telegrams_recipient
(cost=0.00..8.67 rows=536 width=0) (actual time=0.033..0.033 rows=1
loops=1)"
" Index Cond: (recipient_id = 508933)"
" -> Bitmap Index Scan on telegrams_user
(cost=0.00..8.67 rows=549 width=0) (actual time=0.069..0.069 rows=4
loops=1)"
" Index Cond: (user_id = 508933)"
"Total runtime: 0.276 ms"
The table contains several millions records and it's just be
reindexed/analyzed.
Are there any parameters I can tune so that pgsql itself chooses the
best plan? :)
# - Memory -
shared_buffers = 256MB
temp_buffers = 32MB
work_mem = 4MB
maintenance_work_mem = 32MB
# - Planner Cost Constants -
seq_page_cost = 1.0
random_page_cost = 2.5
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.00025
effective_cache_size = 20GB
# - Genetic Query Optimizer -
geqo = on
Thanks,
Corin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 21:25 Hannu Krosing <hannu@2ndquadrant.com>
parent: Corin <wakathane@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Hannu Krosing @ 2010-04-11 21:25 UTC (permalink / raw)
To: Corin <wakathane@gmail.com>; +Cc: pgsql-performance
On Sun, 2010-04-11 at 23:12 +0200, Corin wrote:
> Hi,
>
> I'm having a query where the planer chooses a very bad plan.
>
> explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
> AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
> ORDER BY id DESC LIMIT 10 OFFSET 0
>
> "Limit (cost=0.00..1557.67 rows=10 width=78) (actual
> time=0.096..2750.058 rows=5 loops=1)"
> " -> Index Scan Backward using telegrams_pkey on telegrams
> (cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052
> rows=5 loops=1)"
> " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted))
> OR ((user_id = 508933) AND (NOT user_deleted)))"
> "Total runtime: 2750.124 ms"
You could check if creating special deleted_x indexes helps
do
CREATE INDEX tgrm_deleted_recipent_index ON telegrams(recipient_id)
WHERE recipient_deleted=FALSE;
CREATE INDEX tgrm_deleted_user_index ON telegrams(user_id)
WHERE user_deleted=FALSE;
(if on live system, use "CREATE INDEX CONCURRENTLY ...")
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 22:41 Corin <wakathane@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Corin @ 2010-04-11 22:41 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On 11.04.2010 23:18, Scott Marlowe wrote:
> In both instances your number of rows estimated is WAY higher than the
> actual number of rows returned. Perhaps if you increased
> default_statistics_target to 100, 200, 500 etc. re-analyzed, and then
> reun explain analyze again.
>
> Also increasing work_mem might encourage the bitmap index scans to occur.
>
Increasing the statistics >= 500 indeed helped a lot and causes the
planner to choose a good plan. :)
I'm now thinking about increasing the default_statistics_target of the
whole server from the default (100) to 1000, because I have many tables
with similar data. As the size of the table index seems not change at
all, I wonder how much additional storage is needed? I only care about
runtime performance: are inserts/updates affected by this change? Or is
only analyze affected (only run once during the night)?
Thanks,
Corin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 22:44 Corin <wakathane@gmail.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 8+ messages in thread
From: Corin @ 2010-04-11 22:44 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: pgsql-performance
On 11.04.2010 23:22, Luke Lonergan wrote:
> Try random_page_cost=100
>
Increasing random_page_const to 100 (it was 2.5 before) did not help,
but lowering it <=1.5 helped.
As almost the whole dataset fit's into memory, I think I'll change it
permanently to 1.5 (seq_page is 1.0).
I'll also increase the default_statistics to 1000, because this also
seems to help a lot.
Thanks,
Corin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-11 22:49 Scott Marlowe <scott.marlowe@gmail.com>
parent: Corin <wakathane@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2010-04-11 22:49 UTC (permalink / raw)
To: Corin <wakathane@gmail.com>; +Cc: pgsql-performance
On Sun, Apr 11, 2010 at 4:41 PM, Corin <wakathane@gmail.com> wrote:
> On 11.04.2010 23:18, Scott Marlowe wrote:
>>
>> In both instances your number of rows estimated is WAY higher than the
>> actual number of rows returned. Perhaps if you increased
>> default_statistics_target to 100, 200, 500 etc. re-analyzed, and then
>> reun explain analyze again.
>>
>> Also increasing work_mem might encourage the bitmap index scans to occur.
>>
>
> Increasing the statistics >= 500 indeed helped a lot and causes the planner
> to choose a good plan. :)
>
> I'm now thinking about increasing the default_statistics_target of the whole
> server from the default (100) to 1000, because I have many tables with
> similar data. As the size of the table index seems not change at all, I
> wonder how much additional storage is needed? I only care about runtime
> performance: are inserts/updates affected by this change? Or is only analyze
> affected (only run once during the night)?
default stats target has more to do with how many distinct values /
ranges of values you have. If your data has a nice smooth curve of
distribution smaller values are ok. Large datasets with very weird
data distributions can throw off the planner.
There's a cost for both analyzing and for query planning. If 500
fixes this table, and all the other tables are fine at 100 then it
might be worth doing an alter table alter column for just this column.
However, then you've got to worry about time spent monitoring and
analyzing queries in the database for if / when they need a higher
stats target.
Also, look at increasing effective cache size if the db fits into
memory. Lowering random page cost helps too.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: planer chooses very bad plan
@ 2010-04-12 10:06 Pierre C <lists@peufeu.com>
parent: Corin <wakathane@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Pierre C @ 2010-04-12 10:06 UTC (permalink / raw)
To: Corin <wakathane@gmail.com>; pgsql-performance
> explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933
> AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))
> ORDER BY id DESC LIMIT 10 OFFSET 0
If you need very fast performance on this query, you need to be able to
use the index for ordering.
Note that the following query will only optimize the first page of results
in the case you want to display BOTH sent and received telegrams.
- Create an index on (recipient_id, id) WHERE NOT recipient_deleted
- Create an index on (user_id, id) WHERE NOT user_deleted
- Drop redundant indexes (recipient_id) and (user_id)
SELECT * FROM (
SELECT * FROM "telegrams" WHERE recipient_id=508933 AND
recipient_deleted=FALSE ORDER BY id DESC LIMIT 10
UNION ALL
SELECT * FROM "telegrams" WHERE user_id=508933 AND user_deleted=FALSE
ORDER BY id DESC LIMIT 10
) AS foo ORDER BY id DESC LIMIT 10;
These indexes will also optimize the queries where you only display the
inbox and outbox, in which case it will be able to use the index for
ordering on any page, because there will be no UNION.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-04-12 10:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-11 21:12 planer chooses very bad plan Corin <wakathane@gmail.com>
2010-04-11 21:18 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-11 22:41 ` Corin <wakathane@gmail.com>
2010-04-11 22:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-11 21:25 ` Hannu Krosing <hannu@2ndquadrant.com>
2010-04-12 10:06 ` Pierre C <lists@peufeu.com>
2010-04-11 21:22 Re: planer chooses very bad plan Luke Lonergan <LLonergan@greenplum.com>
2010-04-11 22:44 ` Corin <wakathane@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox