Re: Poor performance on seq scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux