Re: [PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
On 21/07/2012 00:10, Tom Lane wrote: Claudio Freire writes: Looking at this: "-> Index Scan using idx_message_copies_wk2_date_src_pos_partial on message_copies_wk2 message_copies (cost=0.00..19057.93 rows=52 width=32) (actual time=62.124..5486270.845 rows=387524 loops=1)"

Re: [PERFORM] A very long running query....

2012-07-20 Thread Tom Lane
Claudio Freire writes: > Looking at this: > "-> Index Scan using > idx_message_copies_wk2_date_src_pos_partial on message_copies_wk2 > message_copies (cost=0.00..19057.93 rows=52 width=32) (actual > time=62.124..5486270.845 rows=387524 loops=1)" > "

Re: [PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
On 20/07/2012 22:53, Ioannis Anagnostopoulos wrote: On 20/07/2012 22:33, Rosser Schwarz wrote: On Fri, Jul 20, 2012 at 2:27 PM, Ioannis Anagnostopoulos wrote: On 20/07/2012 22:23, Claudio Freire wrote: Misestimated row counts... did you try running an analyze, or upping statistic targets? I h

Re: [PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
On 20/07/2012 22:33, Rosser Schwarz wrote: On Fri, Jul 20, 2012 at 2:27 PM, Ioannis Anagnostopoulos wrote: On 20/07/2012 22:23, Claudio Freire wrote: Misestimated row counts... did you try running an analyze, or upping statistic targets? I have run analyse every so often. I think the problem

Re: [PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
On 20/07/2012 22:33, Claudio Freire wrote: On Fri, Jul 20, 2012 at 6:27 PM, Ioannis Anagnostopoulos wrote: On 20/07/2012 22:23, Claudio Freire wrote: On Fri, Jul 20, 2012 at 6:19 PM, Ioannis Anagnostopoulos wrote: "-> Nested Loop (cost=0.00..20942.93 rows=53 width=144) (actual time

Re: [PERFORM] A very long running query....

2012-07-20 Thread Claudio Freire
On Fri, Jul 20, 2012 at 6:27 PM, Ioannis Anagnostopoulos wrote: > On 20/07/2012 22:23, Claudio Freire wrote: >> >> On Fri, Jul 20, 2012 at 6:19 PM, Ioannis Anagnostopoulos >> wrote: >>> >>> "-> Nested Loop (cost=0.00..20942.93 rows=53 width=144) (actual >>> time=62.174..17783236.718 row

Re: [PERFORM] A very long running query....

2012-07-20 Thread Rosser Schwarz
On Fri, Jul 20, 2012 at 2:27 PM, Ioannis Anagnostopoulos wrote: > On 20/07/2012 22:23, Claudio Freire wrote: >> Misestimated row counts... did you try running an analyze, or upping >> statistic targets? > I have run analyse every so often. I think the problem is that as I get 16K > new rows every

Re: [PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
On 20/07/2012 22:23, Claudio Freire wrote: On Fri, Jul 20, 2012 at 6:19 PM, Ioannis Anagnostopoulos wrote: "-> Nested Loop (cost=0.00..20942.93 rows=53 width=144) (actual time=62.174..17783236.718 rows=387105 loops=1)" " Join Filter: (feed_all_y2012m07.message_copies.msg_

Re: [PERFORM] A very long running query....

2012-07-20 Thread Claudio Freire
On Fri, Jul 20, 2012 at 6:19 PM, Ioannis Anagnostopoulos wrote: > "-> Nested Loop (cost=0.00..20942.93 rows=53 width=144) (actual > time=62.174..17783236.718 rows=387105 loops=1)" > " Join Filter: (feed_all_y2012m07.message_copies.msg_id = > feed_all_y2012m07.ship_pos_messag

[PERFORM] A very long running query....

2012-07-20 Thread Ioannis Anagnostopoulos
Hello, the following query seems to take ages to get executed. However I am more than sure (as you can see from the explain analyse) that uses all the correct indexes. In general I have serious issues with joins in my database. This is a Postgres ver. 9.0 running postgis with the "_int.sql" co

Re: [PERFORM] queries are fast after dump->restore but slow again after some days dispite vacuum

2012-07-20 Thread Laszlo Nagy
Are you running a lot of full table updates? If you mean updates which are applied on every or almost every row of the table - yes, it happens with two rather small tables of max. 10 000 rows. But they are both not touched by the query with this big performance difference. I'm not an expert, but