Poor choice of backward scan - Mailing list 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