[PERFORM] Understanding Hash Join performance

A query I has spends a long time on Hash Joins (and Hash Left Joins). I have a few questions: 1. When does Postgres decide to do a Hash Join, over another type of Join? 2. Do Hash Joins normally perform poorly? What can I do to speed them up? 3. What can I do to enable Postgres to use a faster ty

[PERFORM] CLUSTER versus a dedicated table

Hi. I'm interested in understanding the differences between CLUSTERing a table and making a dedicated one. We have a table with about 1 million records. On a given day, only about 1% of them are of interest. That 1% changes every day (it's WHERE active_date = today), and so we index and cluster

Re: [PERFORM] Will Postgres ever lock with read only queries?

Thanks for the replies. I'm running Postgres 8.2 on Windows XP, Intel Core Duo (though Postgres seems to use only one 1 core). The queries are self joins on very large tables, with lots of nested loops. On Mon, Jul 27, 2009 at 9:40 PM, Tom Lane wrote: > Chris writes: > > Robe

[PERFORM] Will Postgres ever lock with read only queries?

Hi. I'm seeing some weird behavior in Postgres. I'm running read only queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at all). I can run one rather complicated query and the results come back... eventually. Likewise with another. But, when I run both queries at the same ti

Re: [PERFORM] Can Postgres use an INDEX over an OR?

Query is: 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 dict is indexed on word 2009/7/20 Chris > 2009/7/20 Robert James srobertja...@gmail.com&

Re: [PERFORM] Fastest char datatype

Is there a way to use a more compact encoding? I only need 4 bits per char - that would certainly help caching. (I have indexes tuned very well, already). On Mon, Jul 20, 2009 at 2:02 AM, Peter Eisentraut wrote: > On Monday 20 July 2009 04:46:53 Robert James wrote: > > I'm st

[PERFORM] Fastest char datatype

I'm storing a lot of words in a database. What's the fastest format for finding them? I'm going to be doing a lot of WHERE w LIKE 'marsh%' and WHERE w IN ('m', 'ma'). All characters are lowercase a-z, no punctuation, no other alphabets. By default I'm using varchar in utf-8 encoding, but was won

[PERFORM] Can Postgres use an INDEX over an OR?

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 m