On Mon, Apr 8, 2013 at 10:02 AM, Mark Davidson wrote:
> Been trying to progress with this today. Decided to setup the database on
> my local machine to try a few things and I'm getting much more sensible
> results and a totally different query plan http://explain.depesz.com/s/KGdin
> this case t
On Sun, Apr 7, 2013 at 3:22 PM, Mark Davidson wrote:
> Takes a little longer with the INNER join unfortunately. Takes about ~3.5
> minutes, here is the query plan http://explain.depesz.com/s/EgBl.
>
> With the JOIN there might not be a match if the data does not fall within
> one of the areas tha
On Fri, Apr 5, 2013 at 8:51 AM, Mark Davidson wrote:
> Hi All,
>
> Hoping someone can help me out with some performance issues I'm having
> with the INDEX on my database. I've got a database that has a data table
> containing ~55,000,000 rows which have point data and an area table
> containing ~
Hi Jeff,
I'ved tried this test using the -S flag './pgbench -c 4 -j 2 -T 600 -S
pgbench'
Desktop gives me
./pgbench -c 4 -j 2 -T 600 -S pgbench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 2
duration: 600 s
numb
Sorry Vasillis I missed you asking that I just did './pgbench -i pgbench'
didn't specific set any values. I can try some specific ones if you can
suggest any.
On 8 April 2013 21:28, Vasilis Ventirozos wrote:
>
>
>
> On Mon, Apr 8, 2013 at 11:18 PM, Mark Davidson wrote:
>
>> Wow my results are
On Mon, Apr 8, 2013 at 12:31 PM, Mark Davidson wrote:
> Thanks for your response Vasillis. I've run pgbench on both machines
> `./pgbench -c 10 -t 1 pgbench` getting 99.800650 tps on my local
> machine and 23.825332 tps on the server so quite a significant difference.
>
These results are alm
On Mon, Apr 8, 2013 at 11:18 PM, Mark Davidson wrote:
> Wow my results are absolutely appalling compared to both of those which is
> really interesting. Are you running postgres 9.2.4 on both instances? Any
> specific configuration changes?
> Thinking there must be something up with my setup to b
Wow my results are absolutely appalling compared to both of those which is
really interesting. Are you running postgres 9.2.4 on both instances? Any
specific configuration changes?
Thinking there must be something up with my setup to be getting such a low
tps compared with you.
On 8 April 2013 21:
-c 10 means 10 clients so that should take advantage of all your cores (see
bellow)
%Cpu0 : 39.3 us, 21.1 sy, 0.0 ni, 38.7 id, 0.9 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 38.0 us, 25.0 sy, 0.0 ni, 26.0 id, 4.2 wa, 0.0 hi, 6.8 si, 0.0 st
%Cpu2 : 39.3 us, 20.4 sy, 0.0 ni, 39.0 id, 1.3 wa, 0.0 hi, 0.0 si,
Thanks for your response Vasillis. I've run pgbench on both machines
`./pgbench -c 10 -t 1 pgbench` getting 99.800650 tps on my local
machine and 23.825332 tps on the server so quite a significant difference.
Could this purely be down to the CPU clock speed or is it likely something
else causin
Hello Mark,
PostgreSQL currently doesn't support parallel query so a faster cpu even if
it has less cores would be faster for a single query, about benchmarking
you can try pgbench that you will find in the contrib,
the execution plan may be different because of different statistics, have
you analy
Been trying to progress with this today. Decided to setup the database on
my local machine to try a few things and I'm getting much more sensible
results and a totally different query plan
http://explain.depesz.com/s/KGdin this case the query took about a
minute but does sometimes take around
80 se
Takes a little longer with the INNER join unfortunately. Takes about ~3.5
minutes, here is the query plan http://explain.depesz.com/s/EgBl.
With the JOIN there might not be a match if the data does not fall within
one of the areas that is selected in the IN query.
So if we have data id (10) that
Greg Williamson wrote:
>> Thanks for your response. I tried doing what you suggested so
>> that table now has a primary key of
>> ' CONSTRAINT data_area_pkey PRIMARY KEY(area_id , data_id ); '
>> and I've added the INDEX of
>> 'CREATE INDEX data_area_data_id_index ON data_area USING btree (data_i
> Thanks for your response. I tried doing what you suggested so that table now
> has a primary key of ' CONSTRAINT data_area_pkey PRIMARY KEY(area_id ,
> data_id ); ' and I've added the INDEX > of 'CREATE INDEX
> data_area_data_id_index ON data_area USING btree (data_id );' unfortunately
> it h
Hi Kevin
Thanks for your response. I tried doing what you suggested so that table
now has a primary key of ' CONSTRAINT data_area_pkey PRIMARY KEY(area_id ,
data_id ); ' and I've added the INDEX of 'CREATE INDEX
data_area_data_id_index ON data_area USING btree (data_id );' unfortunately
it hasn't
Mark Davidson wrote:
> CONSTRAINT data_area_pkey PRIMARY KEY (data_id , area_id ),
So the only index on this 250 million row table starts with the ID
of the point, but you are joining to it by the ID of the area.
That's requires a sequential scan of all 250 million rows. Switch
the order of t
Hi All,
Hoping someone can help me out with some performance issues I'm having with
the INDEX on my database. I've got a database that has a data table
containing ~55,000,000 rows which have point data and an area table
containing ~3,500 rows which have polygon data. A user queries the data by
sel
18 matches
Mail list logo
