Guillaume Cottenceau wrote:
Laszlo Nagy <gandalf 'at' designaproduct.biz> writes:
Probably, but PostgreSQL doesn't know how to do that. Even if it
did, it depends on how many matches there is. If you scan the index
and then fetch the matching rows from the heap, you're doing random
I/O to the heap. That becomes slower than scanning the heap
sequentially if you're going to get more than a few hits.
I have 700 000 rows in the table, and usually there are less than 500
hits. So probably using a "seq index scan" would be faster. :-) Now I
You can confirm this idea by temporarily disabling sequential
scans. Have a look at this chapter:
I don't think it will anyway do a "seq index scan" as Laszlo envisions.
PostgreSQL cannot do "fetch index tuple and apply %Mug% to it. If it
matches, fetch heap tuple". Even if you disable sequential scans, it's
still going to fetch every heap tuple to see if it matches "%Mug%". It's
just going to do it in index order, which is slower than a seq scan.
BTW: in addition to setting enable_seqscan=false, you probably have to
add a dummy where-clause like "name > ''" to force the index scan.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
