Thread: planer chooses very bad plan

planer chooses very bad plan

From
Corin
Date:
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


Re: planer chooses very bad plan

From
Scott Marlowe
Date:
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.

Re: planer chooses very bad plan

From
Luke Lonergan
Date:
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

Re: planer chooses very bad plan

From
Corin
Date:
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


Re: planer chooses very bad plan

From
Corin
Date:
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


Re: planer chooses very bad plan

From
Scott Marlowe
Date:
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.

Re: planer chooses very bad plan

From
"Pierre C"
Date:
> 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.


Re: planer chooses very bad plan

From
Hannu Krosing
Date:
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