On Aug 17, 2007, at 9:28 AM, hubert depesz lubaczewski wrote:
…
try to change the query to:
SELECT * FROM movies WHERE letter = 'T' ORDER BY letter ASC, name
ASC LIMIT 100
OFFSET 1900;
Thanks for the suggestion, however executing this query takes even
longer regardless of work_mem. The query leads to this plan:
Limit (cost=4320.68..4320.93 rows=100 width=48) (actual
time=2137.764..2138.294 rows=100 loops=1)
-> Sort (cost=4315.93..4351.49 rows=14221 width=48) (actual
time=2129.755..2136.184 rows=2000 loops=1)
Sort Key: letter, name
-> Bitmap Heap Scan on movies (cost=90.77..3067.54
rows=14221 width=48) (actual time=20.277..89.913 rows=13640 loops=1)
Recheck Cond: (letter = 'T'::bpchar)
-> Bitmap Index Scan on movies_letter
(cost=0.00..90.77 rows=14221 width=0) (actual time=18.139..18.139
rows=13644 loops=1)
Index Cond: (letter = 'T'::bpchar)
Total runtime: 2143.111 ms
To compare, that same query (sorting by two columns) without bitmap
scan runs like this:
Limit (cost=5025.26..5289.75 rows=100 width=48) (actual
time=14.986..15.911 rows=100 loops=1)
-> Index Scan using movies_letter_name on movies
(cost=0.00..37612.76 rows=14221 width=48) (actual time=0.125..13.686
rows=2000 loops=1)
Index Cond: (letter = 'T'::bpchar)
Total runtime: 16.214 ms
I'm not an expert at how the planner decides which query plan to use,
but it seems that in my (corner?) case bitmap scan shouldn't be
preferred over the index scan, as the index is pre-sorted and spans
all columns involved in the 'WHERE' and 'ORDER BY' clauses.
Regarding the sort performance and work_mem size I have tested the
same scenario on a second machine (dual P3-1.13 GHz) with work_mem
set to 8192, using the same PostgreSQL version. The query plans are
identical and running times are ~300ms for the bitmap scan and ~5ms
for index scan.
Sincerely,
Frank
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
