Poor choice of backward scan
От | Jim Nasby |
---|---|
Тема | Poor choice of backward scan |
Дата | |
Msg-id | 7ead14dc-2bb1-0f20-dc05-93804d118798@BlueTreble.com обсуждение исходный текст |
Список | pgsql-performance |
Came across this from a client today. Was able to work around it with a fence, but wanted to report it for the next time Robert generates statistics on planner problems. ;) It appears the problem is the planner couldn't recognize that even though there's ~400k rows for user 3737558, very few of them will actually match the rest of the predicates (specifically m_ident). > data=> explain analyze SELECT id FROM table_name WHERE user_id = ‘36’ and m_ident= 'x12345' AND deleted IS NULL ORDER BYchanged DESC LIMIT 1; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Limit (cost=240.31..240.32 rows=1 width=12) (actual time=0.188..0.189 rows=1 loops=1) > -> Sort (cost=240.31..240.32 rows=1 width=12) (actual time=0.187..0.187 rows=1 loops=1) > Sort Key: changed > Sort Method: quicksort Memory: 25kB > -> Index Scan using table_name__user_id_deleted on table_name (cost=0.56..240.30 rows=1 width=12) (actual time=0.131..0.178rows=2 loops=1) > Index Cond: ((user_id = 36) AND (deleted IS NULL)) > Filter: ((m_ident)::text = 'x12345'::text) > Rows Removed by Filter: 63 > Planning time: 0.371 ms > Execution time: 0.357 ms > > (10 rows) > > > data=> explain analyze SELECT id FROM table_name WHERE user_id = '3737558' AND m_ident = 'xxx1234' AND deleted IS NULLORDER BY changed DESC LIMIT 1; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Limit (cost=0.43..177673.83 rows=1 width=12) (actual time=17151.010..17151.010 rows=0 loops=1) > -> Index Scan Backward using table_name___changed on table_name (cost=0.43..888367.40 rows=5 width=12) (actual time=17151.010..17151.010rows=0 loops=1) > Filter: ((deleted IS NULL) AND (user_id = 3737558) AND ((m_ident)::text = 'xxx1234'::text)) > Rows Removed by Filter: 16238592 > Planning time: 0.189 ms > > Execution time: 17151.042 ms > > (6 rows) > > With fence... > > data=> EXPLAIN ANALYZE SELECT id FROM (SELECT * FROM table_name WHERE user_id = 3737558 AND m_ident = 'xxx1234' AND deletedIS NULL OFFSET 0) a ORDER BY changed DESC LIMIT 1; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=221150.73..221150.74 rows=1 width=12) (actual time=1391.148..1391.148 rows=0 loops=1) > -> Sort (cost=221150.73..221150.75 rows=6 width=12) (actual time=1391.147..1391.147 rows=0 loops=1) > Sort Key: a.changed > Sort Method: quicksort Memory: 25kB > -> Subquery Scan on a (cost=4414.63..221150.70 rows=6 width=12) (actual time=1391.115..1391.115 rows=0 loops=1) > -> Bitmap Heap Scan on table_name (cost=4414.63..221150.64 rows=6 width=170) (actual time=1391.113..1391.113rows=0 loops=1) > Recheck Cond: ((user_id = 3737558) AND (deleted IS NULL)) > Filter: ((m_ident)::text = 'AAL3979'::text) > Rows Removed by Filter: 386150 > Heap Blocks: exact=119205 > -> Bitmap Index Scan on table_name__user_id_deleted (cost=0.00..4414.63 rows=247407 width=0) (actualtime=150.593..150.593 rows=397748 loops=1) > Index Cond: ((user_id = 3737558) AND (deleted IS NULL)) > Planning time: 1.613 ms > Execution time: 1392.732 ms > (14 rows) > > Relevant indexes: > > "table_name__enabled_date_end_enabled" btree (date_end, enabled) > "table_name__user_id" btree (user_id) > "table_name__user_id_deleted" btree (user_id, deleted) > "table_name___changed" btree (changed) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
В списке pgsql-performance по дате отправления: