Re: [PERFORM] query plan worse after analyze

2007-10-05 Thread Jeff Frost
On Sat, 6 Oct 2007, Tom Lane wrote: "Jeff Frost" <[EMAIL PROTECTED]> writes: Before analyze it seems to choose Bitmap Heap Scan on episodes current_episode, but after it chooses Index Scan Backward using index_episodes_on_publish_on on episodes current_episode. Have you tried raising the stat

Re: [PERFORM] query plan worse after analyze

2007-10-05 Thread Tom Lane
"Jeff Frost" <[EMAIL PROTECTED]> writes: > Before analyze it seems to choose Bitmap Heap Scan on episodes > current_episode, but after it chooses Index Scan Backward using > index_episodes_on_publish_on on episodes current_episode. Have you tried raising the stats target for "episodes"? Seems lik

Re: [PERFORM] query plan worse after analyze

2007-10-05 Thread Jeff Frost
On Fri, 5 Oct 2007, Stephen Frost wrote: * Jeff Frost ([EMAIL PROTECTED]) wrote: Here are the plans: It's probably just me but, honestly, I find it terribly frustrating to try and read a line-wrapped explain-analyze output... I realize it might not be something you can control in your mailer

Re: [PERFORM] query plan worse after analyze

2007-10-05 Thread Rodrigo De León
On 10/5/07, Stephen Frost <[EMAIL PROTECTED]> wrote: > ... (perhaps a pastebin like http://pgsql.privatepaste.com) ... This is cool. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index

Re: [PERFORM] query plan worse after analyze

2007-10-05 Thread Stephen Frost
* Jeff Frost ([EMAIL PROTECTED]) wrote: > Here are the plans: It's probably just me but, honestly, I find it terribly frustrating to try and read a line-wrapped explain-analyze output... I realize it might not be something you can control in your mailer, but you might consider putting the various

[PERFORM] query plan worse after analyze

2007-10-05 Thread Jeff Frost
Postgresql: 8.2.4 and 8.2.5 I've run into a really strange problem. I have a query that runs much slower after analyze. I load the DB, then run the query and it runs in about 200ms. I then analyze the DB, run the query again and it takes about 1500ms. Before analyze it seems to choose Bitmap H

Re: [PERFORM] Problems with + 1 million record table

2007-10-05 Thread Shane Ambler
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cláudia Macedo Amorim wrote: I'm new in PostGreSQL and I need some help. I have a table with ~2 million records. Queries in this table are too slow and some are not completed.I think it must be a simple question to solve but,

Re: [PERFORM] [GENERAL] Slow TSearch2 performance for table with 1 million documents.

2007-10-05 Thread Benjamin Arai
On Oct 5, 2007, at 8:32 AM, Oleg Bartunov wrote: On Fri, 5 Oct 2007, Tom Lane wrote: Benjamin Arai <[EMAIL PROTECTED]> writes: # explain analyze select * FROM fulltext_article, to_tsquery ('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, q) DESC; QUERY PLAN -

Re: [PERFORM] Problems with + 1 million record table

2007-10-05 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cláudia Macedo Amorim wrote: > I'm new in PostGreSQL and I need some help. > I have a table with ~2 million records. Queries in this table are too slow > and some are not completed.I think it must be a simple question to solve but, > I'm trying witho

Re: [PERFORM] Problems with + 1 million record table

2007-10-05 Thread Arjen van der Meijden
On 5-10-2007 16:34 Cláudia Macedo Amorim wrote: [13236.470] statement_type=0, statement='select a_teste_nestle."CODCLI", a_teste_nestle."CODFAB", a_teste_nestle."CODFAMILIANESTLE", a_teste_nestle."CODFILIAL", a_teste_nestle."CODGRUPONESTLE", a_teste_nestle."CODSUBGRUPONESTLE", a_t

Re: [PERFORM] [GENERAL] Slow TSearch2 performance for table with 1 million documents.

2007-10-05 Thread Oleg Bartunov
On Fri, 5 Oct 2007, Tom Lane wrote: Benjamin Arai <[EMAIL PROTECTED]> writes: # explain analyze select * FROM fulltext_article, to_tsquery ('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, q) DESC; QUERY PLAN ---

Re: [PERFORM] [GENERAL] Slow TSearch2 performance for table with 1 million documents.

2007-10-05 Thread Tom Lane
Benjamin Arai <[EMAIL PROTECTED]> writes: > # explain analyze select * FROM fulltext_article, to_tsquery > ('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, q) DESC; >QUERY PLAN > ---

Re: [PERFORM] Problems with + 1 million record table

2007-10-05 Thread Scott Marlowe
On 10/5/07, Cláudia Macedo Amorim <[EMAIL PROTECTED]> wrote: > > I'm new in PostGreSQL and I need some help. > > I have a table with ~2 million records. Queries in this table are too slow > and some are not completed. > I think it must be a simple question to solve but, I'm trying without > success

[PERFORM] Problems with + 1 million record table

2007-10-05 Thread Cláudia Macedo Amorim
I'm new in PostGreSQL and I need some help. I have a table with ~2 million records. Queries in this table are too slow and some are not completed.I think it must be a simple question to solve but, I'm trying without success. I'm worried because next week I will need to work with tables with ~100

[PERFORM] Slow TSearch2 performance for table with 1 million documents.

2007-10-05 Thread Benjamin Arai
Hi, I have very slow performance for a TSearch2 table. I have pasted the EXPLAIN ANALYZE queries below. 12 seconds is slow for almost any purpose. Is there any way to speed this up? # explain analyze select * FROM fulltext_article, to_tsquery ('simple','dog') AS q WHERE idxfti @@ q ORD