On 07/08/2012 17:00, Jeff Janes wrote:
On Mon, Aug 6, 2012 at 8:08 AM, Ioannis Anagnostopoulos
wrote:
Hi, my query is very simple:
select
msg_id,
msg_type,
ship_pos_messages.pos_georef1,
ship_pos_messages.pos_georef2
Offhand I'd have thought that ANALYZE would gather stats on the
date_trunc expression (because it is indexed) and then you should get
something reasonably accurate for a comparison to a constant.
"Reasonably accurate" meaning "not off by two orders of magnitude".
Practically all of your runtime is
On 06/08/2012 16:34, Tom Lane wrote:
Ioannis Anagnostopoulos writes:
I think this is a pretty good plan and quite quick given the
size of the table (88Million rows at present). However in real
life the parameter where I search for msg_id is not an array of
3
indexed e.g. last
week or last month or location or something, you might do better if
the data does exhibit disk locality. If the data really is scattered,
then a seq scan really will be quicker.
Regards, David
On 06/08/12 23:08, Ioannis Anagnostopoulos wrote:
Hi, my query is very simple
Hi, my query is very simple:
select
msg_id,
msg_type,
ship_pos_messages.pos_georef1,
ship_pos_messages.pos_georef2,
ship_pos_messages.pos_georef3,
ship_pos_messages.pos_georef4,
obj_id,
ship_speed,
On 24/07/2012 15:30, Craig James wrote:
On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos
mailto:ioan...@anatec.com>> wrote:
Hello,
The Postres 9.0 database we use gets about 20K inserts per minute.
As long as you don't query at the same time the database is
Hello,
The Postres 9.0 database we use gets about 20K inserts per minute. As
long as you don't query at the same time the database is copying fine.
However long running queries seems to delay so much the db that the
application server buffers the incoming data as it cannot insert them
fast eno
On 21/07/2012 21:11, Claudio Freire wrote:
On Sat, Jul 21, 2012 at 5:10 PM, Claudio Freire wrote:
wrote:
(feed_all_y2012m07.ship_pos_messages join
ais_server.ship_objects on (ship_pos_messages.obj_id = ship_objects.obj_id))
on (message_copies.msg_id = ship_pos_messag
On 21/07/2012 20:19, Claudio Freire wrote:
On Sat, Jul 21, 2012 at 4:16 PM, Ioannis Anagnostopoulos
wrote:
I am not sure that I can see an improvement, at least on src_id that have
lots of msg_id per day the query never returned even 5 hours later running
"exaplain analyze". For smal
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)"
On 21/07/2012 17:58, Tom Lane wrote:
[ Please try to trim quotes when replying. People don't want to re-read
the entire thread in every message. ]
Ioannis Anagnostopoulos writes:
On 21/07/2012 10:16, Marc Mamin wrote:
isn't the first test superfluous here ?
where extract(
t used.
As Tom already mentioned it, it may make sense not to concatenate the
georef within the index, but keep them separated, or even keep them in
different indexes.
Which is the best depend on the other queries running against this table
HTH,
Marc Mamin
-Original Message-
From: pgsql-
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)"
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
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
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)
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:
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
18 matches
Mail list logo
