[SQL] CREATE INDEX on column of type 'point'
As I have discovered, there is no way to just create index on a column of type 'point' - postgres complains about not knowing the default operator class, no matter what index type I use. Now, my table looks like this: CREATE TABLE places ( place_id integer primary key, coordinates point, value integer, owner_id integer ); owner_id is foreign-keyed to the owners table and there is an index on that column. Now, my queries would search for places that are of certain value, maybe owned by certain owner(s), in 'range' within specified circle. Something like this: SELECT * FROM places WHERE coordinates <@ '<(320,200),200>'::circle AND value BETWEEN 27 AND 80; I get a sequential scan on that table. Reading trough the mailinglist archives I found suggestion Tom Lane made, saying that I should create functional index on table places create index ix_coords on places using gist (circle(coordinates, 0)); And then change the WHERE part of my query like this: WHERE circle(coordinates, 0) <@ '<(320,200),200'>::circle AND value BETWEEN 27 AND 80; Am I better of using 'circle' as data type for column 'coordinates'? Are there any other options? I know there is PostGIS, but that seems like a quite a big overhead. I'll only be checking if some point is in our out of some circle. Mario -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ordered by join? ranked aggregate? how to?
On 15 Wrz, 09:56, andreas.kretsch...@schollglas.com ("A. Kretschmer")
wrote:
> In response to wstrzalka :
>
>
>
> > What I need is to join 2 tables
>
> > CREATE TABLE master(
> > id INT4
> > );
>
> > CREATE TABLE slave (
> > master_id INT4,
> > rank INT4,
> > value TEXT);
>
> > What I need is to make the query:
>
> > SELECT m.id, array_agg(s.value) AS my_problematic_array
> > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id)
> > GROUP BY m.id;
>
> Faster solution (compared to my other email):
>
> test=# select id, array_agg(value) from (SELECT m.id, s.value FROM
> master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2)
> foo group by 1;
> id | array_agg
> +---
> 1 | {3,5,7}
> 2 | {NULL}
> (2 rows)
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
>
> --
> Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-sql
Yes. And No :)
The problem is that in original query the aggregate can be used more
then once :/
So in fact it's like:
SELECT m.id, array_agg(s1.value), array_agg(s2.value)
FROM master AS m LEFT JOIN slave AS s1 ON (m.id = s1.master_id AND
SOME_CONDITION_ON_S1)
LEFT JOIN slave AS s2 ON (m.id =
s2.master_id AND SOME_OTHER_CONDITION_ON_S1)
GROUP BY m.id;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] hardware information
On Sep 15, 3:38 pm, "Martie Krukkeland" wrote: > top ?? > > "std pik" schreef in > berichtnews:80e9c801-4609-49c5-8bc8-d19e08cca...@33g2000vbe.googlegroups.com... > > > > > Hello all.. > > I'm using PostgreSQL 8.3.. > > How can I get information about the hardware utilization: > > - CPU usage. > > - Disk space. > > - Memory allocation. > > thank you. I asked the same question about Hardware. You can find some information here: http://www.westnet.com/~gsmith/content/postgresql/ http://wiki.postgresql.org/wiki/Performance_Optimization In this document (in the url: http://www.westnet.com/~gsmith/content/postgresql/ ) you can find some of the answers you need: Database Hardware Benchmarking (slides) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ordered by join? ranked aggregate? how to?
On 15 Wrz, 10:10, wstrzalka wrote:
> On 15 Wrz, 09:56, andreas.kretsch...@schollglas.com ("A. Kretschmer")
> wrote:
>
>
>
> > In response to wstrzalka :
>
> > > What I need is to join 2 tables
>
> > > CREATE TABLE master(
> > > id INT4
> > > );
>
> > > CREATE TABLE slave (
> > > master_id INT4,
> > > rank INT4,
> > > value TEXT);
>
> > > What I need is to make the query:
>
> > > SELECT m.id, array_agg(s.value) AS my_problematic_array
> > > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id)
> > > GROUP BY m.id;
>
> > Faster solution (compared to my other email):
>
> > test=# select id, array_agg(value) from (SELECT m.id, s.value FROM
> > master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2)
> > foo group by 1;
> > id | array_agg
> > +---
> > 1 | {3,5,7}
> > 2 | {NULL}
> > (2 rows)
>
> > Andreas
> > --
> > Andreas Kretschmer
> > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
>
> > --
> > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
> > To make changes to your
> > subscription:http://www.postgresql.org/mailpref/pgsql-sql
>
> Yes. And No :)
>
> The problem is that in original query the aggregate can be used more
> then once :/
>
> So in fact it's like:
>
> SELECT m.id, array_agg(s1.value), array_agg(s2.value)
> FROM master AS m LEFT JOIN slave AS s1 ON (m.id = s1.master_id AND
> SOME_CONDITION_ON_S1)
> LEFT JOIN slave AS s2 ON (m.id =
> s2.master_id AND SOME_OTHER_CONDITION_ON_S1)
> GROUP BY m.id;
However it still may work as the order by is the same :) Will try ...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] hardware information
top ?? "std pik" schreef in bericht news:80e9c801-4609-49c5-8bc8-d19e08cca...@33g2000vbe.googlegroups.com... > Hello all.. > I'm using PostgreSQL 8.3.. > How can I get information about the hardware utilization: > - CPU usage. > - Disk space. > - Memory allocation. > thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] hardware information
Hello all.. I'm using PostgreSQL 8.3.. How can I get information about the hardware utilization: - CPU usage. - Disk space. - Memory allocation. thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
