Tom Tamulewicz wrote:
------------------------------------------------------------------------
SELECT p.party_id, p.first_name, p.last_name, pli.address1,
pli.city, pli.state FROM customer as p JOIN address as pli ON (
p.party_id = pli.party_id ) WHERE ( p.void_flag IS NULL OR
p.void_flag = false ) AND (first_name like 'B%') AND (last_name
like 'S%') AND (pli.state like 'M%') AND (pli.city like 'AL%')
ORDER BY last_name, first_name LIMIT 51
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..96.48 rows=1 width=2450) (actual
time=13459.814..13459.814 rows=0 loops=1)
-> Nested Loop (cost=0.00..96.48 rows=1 width=2450) (actual
time=13459.804..13459.804 rows=0 loops=1)
-> Index Scan using idx_last_name on customer p
(cost=0.00..50.22 rows=1 width=1209) (actual
time=57.812..13048.524 rows=2474 loops=1)
Index Cond: (((last_name)::text >= 'S'::character
varying) AND ((last_name)::text < 'T'::character varying) AND
((first_name)::text >= 'B'::character varying) AND
((first_name)::text < 'C'::character varying))
Filter: (((void_flag IS NULL) OR (void_flag =
false)) AND ((first_name)::text ~~ 'B%'::text) AND
((last_name)::text ~~ 'S%'::text))
-> Index Scan using address_pkey on address pli
(cost=0.00..46.23 rows=1 width=1257) (actual time=0.149..0.149
rows=0 loops=2474)
Index Cond: (("outer".party_id = pli.party_id))
Filter: (((state)::text ~~ 'M%'::text) AND
((city)::text ~~ 'AL%'::text))
Total runtime: 13460.292 ms
The problem here is this bit:
-> Index Scan using idx_last_name on customer p (cost=0.00..50.22
rows=1 width=1209) (actual time=57.812..13048.524 rows=2474 loops=1)
Index Cond: (((last_name)::text >= 'S'::character
varying) AND ((last_name)::text < 'T'::character varying) AND
((first_name)::text >= 'B'::character varying) AND ((first_name)::text <
'C'::character varying))
Filter: (((void_flag IS NULL) OR (void_flag = false)) AND
((first_name)::text ~~ 'B%'::text) AND ((last_name)::text ~~ 'S%'::text))
Note that you're getting back 2474 rows, but the planner expects 1. Not
the actual time going from 57 to 13048, it's spending all it's time
looking up each tuple in the index, then in the table. Using a seq scan
would be much faster.
Have you analyzed this table? If so, you might need to up the stats
target on last_name and see if that helps.
