Hi
I have an interesting query to be optimized related to this one [1].
The query definition is: Select all buildings that have more than 1
pharmacies and more than 1 schools within a radius of 1000m.
The problem is that I think that this query is inherently O(n^2). In
fact the solution I propos
On 7 Srpen 2012, 14:01, Stefan Keller wrote:
> Hi
>
> I have an interesting query to be optimized related to this one [1].
>
> The query definition is: Select all buildings that have more than 1
> pharmacies and more than 1 schools within a radius of 1000m.
>
> The problem is that I think that this
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
Your proposal lacks the requirement that it's the same building from
where pharmacies and schools are reachable.
But I think about.
Yours, S.
2012/8/7 Tomas Vondra :
> On 7 Srpen 2012, 14:01, Stefan Keller wrote:
>> Hi
>>
>> I have an interesting query to be optimized related to this one [1].
>>
On 7 Srpen 2012, 14:22, Stefan Keller wrote:
> Your proposal lacks the requirement that it's the same building from
> where pharmacies and schools are reachable.
> But I think about.
I don't know the dataset so I've expected the osm_id to identify the
building - then the intersect should work as A
On Tue, Aug 7, 2012 at 5:01 AM, Stefan Keller wrote:
> Hi
>
> I have an interesting query to be optimized related to this one [1].
>
> The query definition is: Select all buildings that have more than 1
> pharmacies and more than 1 schools within a radius of 1000m.
>
> The problem is that I think
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,
> ship_pos_messages.pos_georef3,
>
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,
ship_pos_me
On Tue, Aug 7, 2012 at 9:06 AM, Ioannis Anagnostopoulos
wrote:
> On 07/08/2012 17:00, Jeff Janes wrote:
>>
>> What happens if you set "enable_seqscan=off" and run the query with
>> the very large list? (This is an experiment, not a recommendation for
>> production use)
>>
>>
>> Cheers,
>>
>> Jeff
I found this discussion from 2005 that says you can drop and restore a
trigger inside a transaction, but that doing so locks the whole table:
http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php
> From: Jeff Davis
>
> It got me curious enough that I tested it, and apparently droping a
On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
> I found this discussion from 2005 that says you can drop and restore a
> trigger inside a transaction, but that doing so locks the whole table:
>
> http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php
>> From: Jeff Davis
>>
>> It go
On Tue, Aug 7, 2012 at 2:15 PM, Merlin Moncure wrote:
> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>> I found this discussion from 2005 that says you can drop and restore a
>> trigger inside a transaction, but that doing so locks the whole table:
>>
>> http://archives.postgresql.org/pgsql
On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>> I found this discussion from 2005 that says you can drop and restore a
>> trigger inside a transaction, but that doing so locks the whole table:
>>
>> http://archives.postgresql.org/pgsql
On Tue, Aug 7, 2012 at 3:45 PM, Jeff Janes wrote:
>> absolutely -- the database needs to guard against other writers to the
>> table doing inserts in the meantime.
>
> But why must it? Why can't other writers simply obey the trigger,
> since its removal has not yet been committed? You could have
On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
>>> I found this discussion from 2005 that says you can drop and restore a
>>> trigger inside a transaction, but that doing so locks the
On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
>
> Obviously this is a very expensive trigger, but one that we can drop
> in a very specific circumstance. But we NEVER want to drop it for
> everyone. It seems like a very reasonable use-case to me.
>
>
Sounds like you should try doing the wo
On Tue, Aug 7, 2012 at 4:39 PM, Craig James wrote:
> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James wrote:
I found this discussion from 2005 that says you can drop and restore a
On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
>>>
>>> IF current_user = 'bulk_writer' THEN
>>> return new;
>>> END IF;
>>>
>>
>> I don't know Craig's case, but often the most expe
On Tue, Aug 7, 2012 at 3:22 PM, Jeff Janes wrote:
> On Tue, Aug 7, 2012 at 2:39 PM, Craig James wrote:
>> On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes wrote:
>>> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure wrote:
IF current_user = 'bulk_writer' THEN
return new;
END IF;
>>
Hi Craig
Clever proposal!
I slightly tried to adapt it to the hstore involved.
Now I'm having a weird problem that PG says that "relation 'p' does not exist".
Why does PG recognize table b in the subquery but not table p?
Any ideas?
-- Stefan
SELECT b.way AS building_geometry
FROM
(SELECT way
On 08/08/2012 04:15 AM, Merlin Moncure wrote:
IF current_user = 'bulk_writer' THEN
return new;
END IF;
... or re-create the trigger with a `WHEN` clause (only available in
newer Pg versions, see CREATE TRIGGER) that excludes the migrated
customer ID. You'd have to do it in a new tx to avoid
On Tue, Aug 7, 2012 at 5:29 PM, Craig Ringer wrote:
> On 08/08/2012 04:15 AM, Merlin Moncure wrote:
>>
>> IF current_user = 'bulk_writer' THEN
>>return new;
>> END IF;
>>
>
> ... or re-create the trigger with a `WHEN` clause (only available in newer
> Pg versions, see CREATE TRIGGER) that exc
On Tue, Aug 7, 2012 at 5:07 PM, Stefan Keller wrote:
> Hi Craig
>
> Clever proposal!
> I slightly tried to adapt it to the hstore involved.
> Now I'm having a weird problem that PG says that "relation 'p' does not
> exist".
> Why does PG recognize table b in the subquery but not table p?
> Any id
Hello
Let say we have a table with 6 million records. There are 16 integer columns
and few text column. It is read-only table so every integer column have an
index. Every record is around 50-60 bytes.
The table name is "Item"
The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres
Hi,
I am planning a Postgres migration from 8.4 to 9.1 to be able to leverage
the replication features available in the 9.1 version. I would like to
understand the following things in this regard:
1. Any good documentation which should help in this upgrade.
2. To be able to rep
On 08/03/2012 05:14 PM, robertha...@o2.pl wrote:
> It is read-only table so every integer column have an index.
First tip: Define the table without the indexes. INSERT your data, and
only after it is inserted create your indexes.
Similarly, if you're making huge changes to the table you shoul
On 08/06/2012 01:38 PM, Rajiv Kasera wrote:
Hi,
I am planning a Postgres migration from 8.4 to 9.1 to be able to
leverage the replication features available in the 9.1 version. I
would like to understand the following things in this regard:
1.Any good documentation which should help in this
27 matches
Mail list logo
