The few 'obvious' things I see :
ID and POLLID aren't of the same type (numeric vs bigint)
TTIME isn't indexed.
And as a general matter, you should stick to native datatypes if you don't
need numeric.
But as said in the other answer, maybe you should redo this schema and use
more consistent datatypes
Anyway, from what I remenber, it's not advised to set up shared buffers that
high for windows (I don't do so much windows myself, so maybe someone will be
better informed).
Anyway you can start by correcting the schema…
On Friday 31 July 2009 07:45:55 pari krishnan wrote:
> Dear All,
>
>
> We are
> using Postgres 8.3.7 in our java application. We are doing performances
> tuning and load testing in our setup. we have noticed that ,some of our
> queries to the database taking long time to return the results.Please
> find our setup details belows.
>
> We observed that postgres is running in windows is slower than the linux .
>
> Machine & Database Details :
>
> Windows configuration:
> 4 GB RAM
> 4*1.6 GHZ
> windows 2008 server standard edition
>
> Postgresql configuration:
>
> shared_buffers: 1 GB
> Effective_cache_size: 2GB
> fsync: off (even we tested this parameter is on ,we observed the same
> slowness )
>
>
> Database Details :
>
> Postgres Database : PostgreSQL 8.3.7.1
> Driver Version : PostgreSQL 8.3 JDBC4 with SSL (build 604)
> We are using 40 database connections.
>
>
> We have few tables which will be having more amount data.While running
> our application STATSDATA table will be created daily with table name
> with date.
> like as STATSDATA8_21_2009
>
> Schema for STATSDATA table
>
> create table STATSDATA8_21_2009(
> POLLID Numeric(19),
> INSTANCE varchar(100),
> TTIME Numeric(19),
> VAL Numeric(13)) ;CREATE INDEX POLLID%_ndx on STATSDATA%(POLLID)
>
> Schema for PolledData
>
> create table PolledData(
> "NAME" varchar(50) NOT NULL ,
> "ID" BIGINT NOT NULL ,
> "AGENT" varchar(50) NOT NULL ,
> "COMMUNITY" varchar(100) NOT NULL ,
> "PERIOD" INTEGER NOT NULL,
> "ACTIVE" varchar(10),
> "OID" varchar(200) NOT NULL,
> "LOGDIRECTLY" varchar(10),
> "LOGFILE" varchar(100),
> "SSAVE" varchar(10),
> "THRESHOLD" varchar(10),
> "ISMULTIPLEPOLLEDDATA" varchar(10),
> "PREVIOUSSEVERITY" INTEGER,
> "NUMERICTYPE" INTEGER,
> "SAVEABSOLUTES" varchar(10),
> "TIMEAVG" varchar(10),
> "PORT" INTEGER,
> "WEBNMS" varchar(100),
> "GROUPNAME" varchar(100),
> "LASTCOUNTERVALUE" BIGINT ,
> "LASTTIMEVALUE" BIGINT ,
> "TIMEVAL" BIGINT NOT NULL ,
> "POLICYNAME" varchar(100),
> "THRESHOLDLIST" varchar(200),
> "DNSNAME" varchar(100),
> "SUFFIX" varchar(20),
> "STATSDATATABLENAME" varchar(100),
> "POLLERNAME" varchar(200),
> "FAILURECOUNT" INTEGER,
> "FAILURETHRESHOLD" INTEGER,
> "PARENTOBJ" varchar(100),
> "PROTOCOL" varchar(50),
> "SAVEPOLLCOUNT" INTEGER,
> "CURRENTSAVECOUNT" INTEGER,
> "SAVEONTHRESHOLD" varchar(10),
> "SNMPVERSION" varchar(10),
> "USERNAME" varchar(30),
> "CONTEXTNAME" varchar(30),
> PRIMARY KEY ("ID","NAME","AGENT","OID"),
> index PolledData0_ndx ( "NAME"),
> index PolledData1_ndx ( "AGENT"),
> index PolledData2_ndx ( "OID"),
> index PolledData3_ndx ( "ID"),
> index PolledData4_ndx ( "PARENTOBJ"),
> )
>
>
> We
> have 300k row's in PolledData Table.In each STATSDATA table ,we have
> almost 12 to 13 million rows. Every one minute interval ,we insert data
> into to STATSDATA table. In our application ,we use insert and select
> query to STATSDATA table at regular interval. Please let us know why
> the below query takes more time to return the results. is there any
> thing we need to do to tune the postgres database ?
>
>
>
>
> Please find explain analyze output.
>
>
> First Query :
>
> postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID,
> PolledData.AGENT, N AME, INSTANCE, TTIME, VAL FROM PolledData,
> STATSDATA8_21_2009 WHERE ( ( PolledDa ta.ID=STATSDATA8_21_2009.POLLID) AND
> ( ( TTIME >= 1250838027454) AND ( TTIME <= 1250838079654) ) ) ) t1;
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------
>----- ------------------------------------------------------------------
> Aggregate (cost=773897.12..773897.13 rows=1 width=0) (actual
> time=17818.410..1 7818.412 rows=1 loops=1)
> -> Merge Join (cost=717526.23..767505.06 rows=2556821 width=0) (actual
> time =17560.469..17801.790 rows=13721 loops=1)
> Merge Cond: (statsdata8_21_2009.pollid =
> ((polleddata.id)::numeric)) -> Sort (cost=69708.44..69742.49 rows=13619
> width=8) (actual time=239 2.659..2416.093 rows=13721 loops=1)
> Sort Key: statsdata8_21_2009.pollid
> Sort Method: quicksort Memory: 792kB
> -> Seq Scan on statsdata8_21_2009 (cost=0.00..68773.27
> rows=136 19 width=8) (actual time=0.077..2333.132 rows=13721 loops=1)
> Filter: ((ttime >= 1250838027454::numeric) AND (ttime
> <= 12 50838079654::numeric))
> -> Materialize (cost=647817.78..688331.92 rows=3241131 width=8)
> (actu al time=15167.767..15282.232 rows=21582 loops=1)
> -> Sort (cost=647817.78..655920.61 rows=3241131 width=8)
> (actua l time=15167.756..15218.645 rows=21574 loops=1)
> Sort Key: ((polleddata.id)::numeric)
> Sort Method: external merge Disk: 736kB
> -> Seq Scan on polleddata (cost=0.00..164380.31
> rows=3241 131 width=8) (actual time=1197.278..14985.665 rows=23474 loops=1)
> Total runtime: 17826.511 ms
> (14 rows)
>
> Second Query :
>
> postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID,
> PolledData.AGENT, N AME, INSTANCE, TTIME, VAL FROM PolledData,
> STATSDATA8_20_2009 WHERE ( ( PolledDa ta.ID=STATSDATA8_20_2009.POLLID) AND
> ( ( TTIME >= 1250767134601) AND ( TTIME <= 1250767384601) ) ) ) t1;
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------
>----- -----------------------------------------------------------------
> Aggregate (cost=1238144.31..1238144.32 rows=1 width=0) (actual
> time=111796.187 ..111796.188 rows=1 loops=1)
> -> Merge Join (cost=1034863.23..1212780.47 rows=10145533 width=0)
> (actual t ime=111685.204..111783.670 rows=13126 loops=1)
> Merge Cond: (statsdata8_20_2009.pollid =
> ((polleddata.id)::numeric)) -> Sort (cost=387045.44..387168.91 rows=49389
> width=8) (actual time=1 09756.892..109770.670 rows=13876 loops=1)
> Sort Key: statsdata8_20_2009.pollid
> Sort Method: quicksort Memory: 799kB
> -> Seq Scan on statsdata8_20_2009 (cost=0.00..382519.60
> rows=49 389 width=8) (actual time=16.898..109698.188 rows=13876 loops=1)
> Filter: ((ttime >= 1250767134601::numeric) AND (ttime
> <= 12 50767384601::numeric))
> -> Materialize (cost=647817.78..688331.92 rows=3241131 width=8)
> (actu al time=1928.266..1960.672 rows=13915 loops=1)
> -> Sort (cost=647817.78..655920.61 rows=3241131 width=8)
> (actua l time=1928.253..1941.423 rows=5830 loops=1)
> Sort Key: ((polleddata.id)::numeric)
> Sort Method: external merge Disk: 744kB
> -> Seq Scan on polleddata (cost=0.00..164380.31
> rows=3241 131 width=8) (actual time=195.961..1724.824 rows=23474 loops=1)
> Total runtime: 111805.644 ms
> (14 rows)
>
> Third Query
>
> postgres=# explain analyze SELECT COUNT(*) FROM ( SELECT ID,
> PolledData.AGENT, N AME, INSTANCE, TTIME, VAL FROM PolledData,
> STATSDATA8_21_2009 WHERE ( ( PolledDa ta.ID=STATSDATA8_21_2009.POLLID) AND
> ( ( TTIME >= 1250838027454) AND ( TTIME <= 1250838027454) ) ) union all
> SELECT ID, PolledData.AGENT, NAME, INSTANCE, TTIM E, VAL FROM PolledData,
> STATSDATA8_20_2009 WHERE ( ( PolledData.ID=STATSDATA8_20 _2009.POLLID) AND
> ( ( TTIME >= 1250767134601) AND ( TTIME <= 1250767134601) ) ) )t1 ;
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------
>----- -----------------------------------------------------------------
> Aggregate (cost=719553.16..719553.17 rows=1 width=0) (actual
> time=603669.894.. 603669.895 rows=1 loops=1)
> -> Append (cost=0.00..719553.15 rows=2 width=0) (actual
> time=12736.956..603 668.946 rows=228 loops=1)
> -> Subquery Scan "*SELECT* 1" (cost=0.00..203804.22 rows=1
> width=0) ( actual time=12736.953..506562.673 rows=227 loops=1)
> -> Nested Loop (cost=0.00..203804.20 rows=1 width=78)
> (actual t ime=12736.949..506561.858 rows=227 loops=1)
> Join Filter: ((public.polleddata.id)::numeric =
> statsdata8_ 21_2009.pollid)
> -> Seq Scan on statsdata8_21_2009
> (cost=0.00..70574.88 ro ws=1 width=32) (actual time=0.047..29066.227
> rows=227 loops=1)
> Filter: ((ttime >= 1250838027454::numeric) AND
> (ttime <= 1250838027454::numeric))
> -> Seq Scan on polleddata (cost=0.00..132939.93
> rows=1929 3 width=54) (actual time=362.780..2066.030 rows=23474 loops=227)
> -> Subquery Scan "*SELECT* 2" (cost=0.00..515748.94 rows=1
> width=0) ( actual time=4855.541..97105.635 rows=1 loops=1)
> -> Nested Loop (cost=0.00..515748.92 rows=1 width=78)
> (actual t ime=4855.537..97105.628 rows=1 loops=1)
> Join Filter: ((public.polleddata.id)::numeric =
> statsdata8_ 20_2009.pollid)
> -> Seq Scan on statsdata8_20_2009
> (cost=0.00..382519.60 r ows=1 width=32) (actual time=3136.008..93985.540
> rows=1 loops=1)
> Filter: ((ttime >= 1250767134601::numeric) AND
> (ttime <= 1250767134601::numeric))
> -> Seq Scan on polleddata (cost=0.00..132939.93
> rows=1929 3 width=54) (actual time=371.394..3087.391 rows=23474 loops=1)
> Total runtime: 603670.065 ms
> (15 rows)
>
> Please let me know if you need any more details in this.
>
>
> Regards,
> Pari
>
>
> Yahoo! recommends that you upgrade to the new and safer Internet
> Explorer 8. http://downloads.yahoo.com/in/internetexplorer/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
