On Wed, 2007-04-18 at 11:06 +0100, Alberto Simões wrote: > On 4/17/07, Alberto Simões <[EMAIL PROTECTED]> wrote: > > On 4/17/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > > > > > > > > Consider the following database schema: > > > > CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, > > > > word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, > > > > word4)); > > > > CREATE INDEX tet_b ON tetragrams (word2); > > > > CREATE INDEX tet_c ON tetragrams (word3); > > > > CREATE INDEX tet_d ON tetragrams (word4); > > > > > > > > The problem is that I want to use: > > > > > > > > SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; > > > > > > > > and it takes.. five minutes and did not give the result yet... > > > > > > > > Is there anything I can do to make it speed up CONSIDERABLY? > > > > > > CREATE INDEX tet_e ON tetragrams(word1, occs); > > I tried: > CREATE INDEX tet_a ON tetragrams(word1, occs); > CREATE INDEX tet_b ON tetragrams(word2, occs); > CREATE INDEX tet_c ON tetragrams(word3, occs); > CREATE INDEX tet_d ON tetragrams(word4, occs); > and PRIMARY KEY(word1,word2,word3,word4) > > This works fairly well if I need > SELECT * from tetragrams WHERE word1 = 'x' ORDER BY occs; > > The problem is that I need as well > SELECT * from tetragrams WHERE word1 = 'x' AND word2 = 'y' ORDER BY occs; > and this is QUITE slow.
You could see if this one is faster: SELECT * FROM tetragrams WHERE word1 = 'x' AND word2||'' = 'y' ORDER BY occs; Should take about the same time as the one that works "fairly well". But return less rows. See also the ANALYZE command. Dan. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
