Re: [PERFORM] Slow query with backwards index scan

* Nis Jørgensen <[EMAIL PROTECTED]> [20070727 20:31]: > How does the "obvious" UNION query do - ie: > > SELECT * FROM ( > SELECT * FROM large_table lt > WHERE lt.user_id = 12345 > > UNION > > SELECT * FROM large_table lt > WHERE user_id IN (SELECT contact_id FROM relationships WHERE user_id=1234

Re: [PERFORM] Vacuum looping?

"Steven Flatt" <[EMAIL PROTECTED]> writes: > The vacuum then just sat there. What I can't understand is why it went back > for a second pass of the pkey index? There was nothing writing to the table > once the vacuum began. Is this behaviour expected? Yes (hint: the numbers tell me what your ma

Re: [PERFORM] Vacuum looping?

On Fri, Jul 27, 2007 at 05:32:11PM -0400, Steven Flatt wrote: > weren't convinced was doing anything), then start a manual vacuum with a > higher vacuum_cost_limit to get things cleaned up quicker. What are your vacuum_cost_* settings? If you set those too aggressively you'll be in big trouble. T

Re: [PERFORM] Slow query with backwards index scan

Tilmann Singer <[EMAIL PROTECTED]> wrote .. > * Nis Jørgensen <[EMAIL PROTECTED]> [20070727 20:31]: > > How does the "obvious" UNION query do - ie: > > > > SELECT * FROM ( > > SELECT * FROM large_table lt > > WHERE lt.user_id = 12345 > > > > UNION > > > > SELECT * FROM large_table lt > > WHERE u

Re: [PERFORM] Slow query with backwards index scan

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [20070728 21:05]: > Let's try putting the sort/limit in each piece of the UNION to speed them up > separately. > > SELECT * FROM ( > (SELECT * FROM large_table lt > WHERE lt.user_id = 12345 > ORDER BY created_at DESC LIM

[PERFORM] select on 1milion register = 6s

Friends, Who can help me? My SELECT in a base with 1 milion register, using expression index = 6seconds. Please, I don't know how to makes it better. Thanks

Re: [PERFORM] select on 1milion register = 6s

Bruno Rodrigues Siqueira wrote: Who can help me? My SELECT in a base with 1 milion register, using expression index = 6seconds… Run your query using EXPLAIN ANALYZE SELECT ... your query ... and then post the results to this newsgroup. Nobody can help until they see the res

Re: [PERFORM] select on 1milion register = 6s

Do you have analyzed your table before doing this ? Le samedi 28 juillet 2007, Bruno Rodrigues Siqueira a écrit : > Friends, > > > > > > > > Who can help me? My SELECT in a base with 1 milion register, > using expression index = 6seconds. > > > > > > Please, I don't know how to makes

Re: [PERFORM] Slow query with backwards index scan

Tilmann Singer wrote: * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [20070728 21:05]: Let's try putting the sort/limit in each piece of the UNION to speed them up separately. SELECT * FROM ( (SELECT * FROM large_table lt WHERE lt.user_id = 12345 ORDER BY created_at DESC LIMIT 10) A

Re: [PERFORM] Slow query with backwards index scan

Tilmann Singer wrote: * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [20070728 21:05]: Let's try putting the sort/limit in each piece of the UNION to speed them up separately. SELECT * FROM ( (SELECT * FROM large_table lt WHERE lt.user_id = 12345 ORDER BY created_at DESC LIMIT 10) A

RES: [PERFORM] select on 1milion register = 6s

Ok. Query EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-mm') as ordem from detalhamento_bas where to_char( data_encerramento ,'-mm') between '2006-12' and '2007-01' GROUP BY opcoes_mes, orde

Re: [PERFORM] Slow query with backwards index scan

As other posters have pointed out, you can overcome the ORDER BY/LIMIT restriction on UNIONs with parentheses. I think I misbalanced the parentheses in my original post, which would have caused an error if you just copied and pasted. I don't think the limitation has to do with planning--just pa

Re: [PERFORM] Slow query with backwards index scan

* Craig James <[EMAIL PROTECTED]> [20070728 22:00]: > >>SELECT * FROM ( > >> (SELECT * FROM large_table lt > >> WHERE lt.user_id = 12345 > >> ORDER BY created_at DESC LIMIT 10) AS q1 > >> UNION > >> (SELECT * FROM large_table lt >

Re: RES: [PERFORM] select on 1milion register = 6s

On lau, 2007-07-28 at 17:12 -0300, Bruno Rodrigues Siqueira wrote: > where > > to_char( data_encerramento ,'-mm') > between '2006-12' and '2007-01' assuming data_encerramento is a date column, try: WHERE data_encerramento between '2006-12-01' and '2007-01-31' gnari -

RES: RES: [PERFORM] select on 1milion register = 6s

Data_encerramento is a timestamp column I will try your tip. Thanks -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Ragnar Enviada em: sábado, 28 de julho de 2007 19:36 Para: Bruno Rodrigues Siqueira Cc: pgsql-performance@postgresql.org Assunto: Re: RES: [

RES: [PERFORM] select on 1milion register = 6s

Yes. Look this... and please, tell me if you can help me... Thanks Query EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-mm') as ordem from detalhamento_bas where to_char( data_encerramento ,'-mm') between '2

RES: [PERFORM] select on 1milion register = 6s

Yes, i do. -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Hervé Piedvache Enviada em: sábado, 28 de julho de 2007 16:57 Para: pgsql-performance@postgresql.org Cc: Bruno Rodrigues Siqueira Assunto: Re: [PERFORM] select on 1milion register = 6s Do you hav

Re: [PERFORM] select on 1milion register = 6s

On 7/28/07, Bruno Rodrigues Siqueira <[EMAIL PROTECTED]> wrote: > > Ok. > QUERY PLAN > Sort (cost=11449.37..11449.40 rows=119 width=8) (actual > time=14431.537..14431.538 rows=2 loops=1) > Sort Key: to_char(data_encerramento, '-mm'::text) > -> HashAggregate (cost=11448.79..11448.96 rows=