From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | Robert James <srobertjames(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Understanding sequential versus index scans. |
Date: | 2009-07-19 23:27:24 |
Message-ID: | 407d949e0907191627s4206eedapfaf48f69aae9350c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jul 19, 2009 at 11:59 PM, Robert James<srobertjames(at)gmail(dot)com> wrote:
> PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)"
>
> On Sun, Jul 19, 2009 at 6:58 PM, Robert James <srobertjames(at)gmail(dot)com>
> wrote:
>>
>> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
>> do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why
>> is this so? And how can I shut this off?
>> select * from dict
>> where
>> word in (select substr('moon', 0, generate_series(3,length('moon')))) --
>> this is my X above
>> OR word like 'moon%' -- this is my Y above
>> Seq Scan on dict (cost=0.02..2775.66 rows=30422 width=24) (actual
>> time=16.635..28.580 rows=8 loops=1)
>> Filter: ((hashed subplan) OR ((word)::text ~~ 'moon%'::text))
>> SubPlan
>> -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.019
>> rows=2 loops=1)
>> Total runtime: 28.658 ms
>> (Using just X or Y alone uses the index, and completes in 0.150 ms)
>> Is this a bug?
Well there are known bugs in 8.2.1 -- that's why the current 8.2
release is 8.2.13.
The next step here is to set enable_seqscan=off and run explain
analyze again. You may have to adjust some costs to sync the estimated
cost with actual run-time.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-07-19 23:47:40 | Re: Understanding sequential versus index scans. |
Previous Message | Robert James | 2009-07-19 22:59:38 | Re: Understanding sequential versus index scans. |