Re: [GENERAL] Understanding sequential versus index scans.

2009-07-20 Thread Robert James
Yes, I had done UNION. UNION ALL achives the expected plan and speed! Thank you! BTW, this is interesting, because there are only about 5 or 6 rows max returned from both queries - but I guess the planner expects more and hence changes the plan to remove duplicates. On Sun, Jul 19, 2009 at 9:05

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-20 Thread Greg Stark
On Mon, Jul 20, 2009 at 2:22 PM, Robert Jamessrobertja...@gmail.com wrote: BTW, this is interesting, because there are only about 5 or 6 rows max returned from both queries - but I guess the planner expects more and hence changes the plan to remove duplicates. If you sent the plans for the

[GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
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

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
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 srobertja...@gmail.comwrote: 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

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Greg Stark
On Sun, Jul 19, 2009 at 11:59 PM, Robert Jamessrobertja...@gmail.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 srobertja...@gmail.com wrote: Hi.  I notice that when I do a WHERE x,

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Tom Lane
Robert James srobertja...@gmail.com writes: 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. It can use indexes for OR conditions, but not for arbitrary OR conditions... select * from dict

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread John R Pierce
Robert James 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? maybe its because you have no index on (X OR Y) ? or maybe because the

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
UNION was better, but still 5 times as slow as either query done individually. set enable_seqscan=off didn't help at all - it was totally ignored Is there anything else I can do? On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert James srobertja...@gmail.com writes:

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Robert James
Is there anyway to tell Postgres Run these two queries, and union their results, but don't change the plan as to a UNION - just run them separately? Something seems funny to me that running a UNION should be twice as slow as running the two queries one after the other. On Sun, Jul 19, 2009 at

Re: [GENERAL] Understanding sequential versus index scans.

2009-07-19 Thread Scott Marlowe
On Sun, Jul 19, 2009 at 6:10 PM, Robert Jamessrobertja...@gmail.com wrote: UNION was better, but still 5 times as slow as either query done individually. set enable_seqscan=off didn't help at all - it was totally ignored Is there anything else I can do? Did you try union, or union all? --