Soni M | 4 Mar 20:31 2015
Picon

slow server : s_lock and _bt_checkkeys on perf top

Hello All,

Master db size 1.5 TB
All postgres 9.1.13 installed from RHEL package.
It has streaming replica and slony replica to another servers.

Server performance is slower than usual, before that, there's a big query got cancelled and then performance get slow.

No sign of IO wait.

on sar, it's %user and %system dominate the cpu usage
01:25:04 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
Average:        all     51.91      0.00     12.03      0.66      0.00     35.39

on perf top, i saw 
 18.93%  postgres                                  [.] s_lock
 10.72%  postgres                                  [.] _bt_checkkeys
almost always at top.

I don't have any idea, what's causing it or how to resolve it ?

Any answer is very appreciated.

--
Regards,

Soni Maula Harriz
Gunnlaugur Thor Briem | 4 Mar 20:03 2015
Picon

EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

Hi, 

we are seeing cases of EXPLAIN INSERT INTO foo SELECT ... taking over an hour, with disk I/O utilization (percent of time device is busy) at 100% the whole time, although I/O bandwidth is not saturated. This is on PostgreSQL 9.1.13.

What could cause this? Note that there is no ANALYZE. Is it possible that the SELECT is actually executed, in planning the INSERT?

When executing the INSERT itself (not EXPLAIN) immediately afterwards, that logs a "temporary file" message, but the EXPLAIN invocation does not (though the disk I/O suggests that a large on-disk sort is occurring):

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp6016.0", size 744103936
STATEMENT:  INSERT INTO f_foo SELECT
[...] 

During that actual execution, there's a lower disk I/O utilization (though a higher MB/s rate).

Charts of disk I/O utilization and rate are at http://postimg.org/image/628h6jmn3/ ... the solid 100% span is the EXPLAIN statement, ending at 6:13:30pm, followed by the INSERT statement ending at 6:32pm. Metrics are collected by New Relic; their definition of I/O utilization is at https://discuss.newrelic.com/t/disk-i-o-metrics/2900

Here's the EXPLAIN statement: 

LOG:  duration: 3928778.823 ms  statement: EXPLAIN INSERT INTO f_foo SELECT
       t_foo.fk_d1,
       t_foo.fk_d2,
       t_foo.fk_d3,
       t_foo.fk_d4,
       t_foo.fk_d5,
       t_foo.fk_d6,
       t_foo.value
FROM t_foo
WHERE NOT (EXISTS
             (SELECT *
              FROM f_foo
              WHERE f_foo.fk_d2 = t_foo.fk_d2
                AND f_foo.fk_d3 = t_foo.fk_d3
                AND f_foo.fk_d4 = t_foo.fk_d4
                AND f_foo.fk_d5 = t_foo.fk_d5
                AND f_foo.fk_d6 = t_foo.fk_d6
                AND f_foo.fk_d1 = t_foo.fk_d1))

(where t_foo is a temp table previously populated using COPY, and the NOT EXISTS subquery refers to the same table we are inserting into)

Here's the EXPLAIN output: 

Insert on f_foo  (cost=8098210.50..9354519.69 rows=1 width=16)
  ->  Merge Anti Join  (cost=8098210.50..9354519.69 rows=1 width=16)
        Merge Cond: ((t_foo.fk_d2 = public.f_foo.fk_d2) AND
                     (t_foo.fk_d3 = public.f_foo.fk_d3) AND
                     (t_foo.fk_d4 = public.f_foo.fk_d4) AND
                     (t_foo.fk_d5 = public.f_foo.fk_d5) AND
                     (t_foo.fk_d6 = public.f_foo.fk_d6) AND
                     (t_foo.fk_d1 = public.f_foo.fk_d1))
        ->  Sort  (cost=3981372.25..4052850.70 rows=28591380 width=16)
              Sort Key: t_foo.fk_d2, t_foo.fk_d3, t_foo.fk_d4, t_foo.fk_d5,
                        t_foo.fk_d6, t_foo.fk_d1
              ->  Seq Scan on t_foo  (cost=0.00..440461.80 rows=28591380
                                      width=16)
        ->  Sort  (cost=4116838.25..4188025.36 rows=28474842 width=12)
              Sort Key: public.f_foo.fk_d2, public.f_foo.fk_d3,
                        public.f_foo.fk_d4, public.f_foo.fk_d5,
                        public.f_foo.fk_d6, public.f_foo.fk_d1
              ->  Seq Scan on f_foo  (cost=0.00..591199.42 rows=28474842
                                      width=12)

The INSERT is indeed rather large (which is why we're issuing an EXPLAIN ahead of it to log the plan). So its long execution time is expected. But I want to understand why the EXPLAIN takes even longer.

The table looks like this: 

\d f_foo
Table "public.f_foo"
 Column |   Type   | Modifiers 
--------+----------+-----------
 fk_d1  | smallint | not null
 fk_d2  | smallint | not null
 fk_d3  | smallint | not null
 fk_d4  | smallint | not null
 fk_d5  | smallint | not null
 fk_d6  | smallint | not null
 value  | integer  | 
Indexes:
    "f_foo_pkey" PRIMARY KEY, btree (fk_d2, fk_d6, fk_d4, fk_d3, fk_d5, fk_d1) CLUSTER
    "ix_f_foo_d4" btree (fk_d4)
    "ix_f_foo_d3" btree (fk_d3)
    "ix_f_foo_d5" btree (fk_d5)
    "ix_f_foo_d6" btree (fk_d6)
Foreign-key constraints:
    "f_foo_d2_fkey" FOREIGN KEY (fk_d2) REFERENCES d2(id) DEFERRABLE
    "f_foo_d6_fkey" FOREIGN KEY (fk_d6) REFERENCES d6(id) DEFERRABLE
    "f_foo_d5_fkey" FOREIGN KEY (fk_d5) REFERENCES d5(id) DEFERRABLE
    "f_foo_d4_fkey" FOREIGN KEY (fk_d4) REFERENCES d4(id) DEFERRABLE
    "f_foo_d3_fkey" FOREIGN KEY (fk_d3) REFERENCES d3(id) DEFERRABLE

Conceivably relevant (though I don't know how): this database has a very large number of table objects (1.3 million rows in pg_class). But other EXPLAINs are not taking anywhere near this long in this DB; the heavy EXPLAIN is only seen on INSERT into this and a couple of other tables with tens of millions of rows.

Any ideas?

Thanks, best regards, 

- Gulli

Paolo Losi | 28 Feb 10:08 2015
Picon

pushing order by + limit to union subqueries

Hi all,
 I've noticed that order by / limit are not distributed to union subqueries
by the planner:

Example:

q1: (select * from t1) union all (select * from t2) order by x limit 10;
q2: (select * from t1 order by x limit 10) union all (select * from t2 order by x limit 10)
      order by x limit 10;

both queries should be equivalent, but the planner provides hugely different
plans. I was expecting that the planner could rewrite the first to the second.
Am I overlooking something? If this is the case, can anyone explain why this
optimization is not performed?

Thanks!
Paolo
Josh Berkus | 28 Feb 02:28 2015

Bad cost estimate with FALSE filter condition

All:

This got posted to pgsql-bugs, but got no attention there[1], so I'm
sending it to this list.

Test case:

createdb bench
pgbench -i -s bench
\c bench

bench=# explain select * from pgbench_accounts where aid = 2;
                                          QUERY PLAN
---------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.42..8.44 rows=1 width=97)
   Index Cond: (aid = 2)
(2 rows)

bench=# explain select * from pgbench_accounts where aid = 2 and false;

                                QUERY PLAN
-------------------------------------------------
 Result  (cost=0.00..26394.00 rows=1 width=97)
   One-Time Filter: false
   ->  Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1 width=97)
(3 rows)

This seems like a special case of the "aborted plan cost", that is, when
the planner expects to abort a plan early, it nevertheless returns the
full cost for the non-aborted version of the query, rather than the
working cost, which is based on the abort.

For example:

bench=# create index on pgbench_accounts(bid);
CREATE INDEX
bench=# explain select * from pgbench_accounts where bid = 2;
                                                QUERY PLAN

----------------------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_bid_idx on pgbench_accounts
(cost=0.42..4612.10 rows=102667 width=97)
   Index Cond: (bid = 2)
(2 rows)

bench=# explain select * from pgbench_accounts where bid = 2 limit 1;
                                   QUERY PLAN

--------------------------------------------------------------------------------
 Limit  (cost=0.00..0.28 rows=1 width=97)
   ->  Seq Scan on pgbench_accounts  (cost=0.00..28894.00 rows=102667
width=97)
         Filter: (bid = 2)
(3 rows)

So in this case, the top-level node returns a lower cost because the
planner knows that it will find a row with bid=2 fairly quickly in the
seq scan.  But in the WHERE FALSE example, that scan *is* the top-level
node, so the planner returns a fictitious cost for the whole query.

Or is there something else at work here?

[1]
http://www.postgresql.org/message-id/20150225194953.2546.86836 <at> wrigleys.postgresql.org

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 25 Feb 23:31 2015

Re: Reverse Key Index

On 02/14/2015 10:35 AM, Sven R. Kunze wrote:
> Thanks for the immediate reply.
> 
> I understand the use case is quite limited.
> 
> On the other hand, I see potential when it comes to applications which
> use PostgreSQL. There, programmers would have to change a lot of code to
> tweak existing (and more importantly working) queries to hash/reverse an
> id column first. Using ORMs would make this change even more painful and
> maybe even impossible.
> 
> When reading
> https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/
> carefully, it also seems to work with index scan partially in case of
> equality comparisons.

Seems like a good use for SP-GiST.  Go for it!

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Saurabh Gupta A | 23 Feb 12:38 2015
Picon

Regarding "Point-in-time Recovery" feature

Hello,

Has anybody used online backup feature of postgreSQL? In fact precise postgreSQL term is called: "Point-in-time Recovery" (PITR)
This means enabling following additional options in config:
---
archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f' # This is only example path
---

 

If yes then may I know how it is used and how it impacts database performance?

 

 

Regards:

Saurabh

 

 

Nicolas Paris | 20 Feb 11:28 2015
Picon

PG 9.3 materialized view VS Views, indexes, shared memory

Hello !

I have a huge table, 1 bilion rows, with many indexes.
I have many materialysed view (MV), subsets of this huge table, with same kind indexes.
I have many users, querying thoses MV.
I have a storage problem, because of MV physical memory use.

I wonder :
If I replace MV with classical Views, the only indexes that will be used will be the huge table's one. As all users will query on the same indexes, is will always be loaded in memory, right ? This will be shared, I mean if 10 users query the same time, will it use 10*ram memory for indexes or juste 1 time that ram ? 

I terms of performances, will MV better than simple Views in my case ?


Thanks for explanation by advance


Nicolas PARIS
Sven R. Kunze | 14 Feb 19:13 2015
Picon

Reverse Key Index

Hi,

does PostgreSQL support the concept of reverse key indexing as described 
here? I couldn't find any documentation on this yet.

http://www.toadworld.com/platforms/oracle/w/wiki/11075.reverse-key-index-from-the-concept-to-internals.aspx

Regards,

-- 
Sven R. Kunze
TBZ-PARIV GmbH, Bernsdorfer Str. 210-212, 09130 Chemnitz
Tel: +49 (0)371 33714721, Fax: +49 (0)371 5347920
e-mail: srkunze <at> tbz-pariv.de
web: www.tbz-pariv.de

Geschäftsführer: Dr. Reiner Wohlgemuth
Sitz der Gesellschaft: Chemnitz
Registergericht: Chemnitz HRB 8543

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Nico Sabbi | 12 Feb 23:25 2015
Picon

Configuration tips for very large database

Hello,
I've been away from  postgres for several years, so please forgive me if 
I forgot nearly everything:-)

I've just inherited a database collecting environmental data. There's a 
background process continually inserting records (not so often, to say 
the truth) and a web interface to query data.
At the moment the record count of the db is 250M and growing all the 
time. The 3 main tables have just 3 columns.

Queries get executed very very slowly, say 20 minutes. The most evident 
problem I see is that io wait load is almost always 90+% while querying 
data, 30-40% when "idle" (so to say).
Obviously disk access is to blame, but I'm a bit surprised because the 
cluster where this db is running is not at all old iron: it's a vmware 
VM with 16GB ram, 4cpu 2.2Ghz, 128GB disk (half of which used). The disk 
system underlying vmware is quite powerful, this postgres is the only 
system that runs slowly in this cluster.
I can increase resources if necessary, but..

Even before analyzing queries (that I did) I'd like to know if someone 
has already succeeded in running postgres with 200-300M records with 
queries running much faster than this. I'd like to compare the current 
configuration with a super-optimized one to identify the parameters that 
need to be changed.
Any link to a working configuration would be very appreciated.

Thanks for any help,
   Nico

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

paulcc | 12 Feb 19:07 2015
Picon

query - laziness of lateral join with function

Hi

I'm using cross join lateral with a non-trivial function in 
an attempt to limit calculation of that function, and am 
wondering about some aspects of how lateral is currently 
implemented. 

NB these queries are generated by a certain ORM, and are
usually embedded in much more complex queries...

Case one: counting

   select count(alpha.id) 
   from alpha
   cross join lateral some_function(alpha.id) as some_val
   where alpha.test

   Here the function is strict, and moreover its argument will never
   be null - hence there should always be a non-null value returned. 

   I would expect that since the function doesn't impact on the 
   number of rows (always one value returned for each row in alpha),
   then I'd hope the function is never called. EXPLAIN shows it being 
   called for each row in the main table. 

Case two: pagination

   select alpha.*, some_val
   from alpha
   cross join lateral some_function(alpha.id) as some_val
   where alpha.test
   order by alpha.name asc
   limit 100 offset 100

   Same setup as above, and I'd expect that the ordering and
   selection of rows can be done first and the function only 
   called on the rows that get selected. Again, EXPLAIN shows
   otherwise.

So: am I expecting too much for LATERAL, or have I missed a 
trick somewhere? 

Many thanks in advance! 

Paul

--
View this message in context: http://postgresql.nabble.com/query-laziness-of-lateral-join-with-function-tp5837706.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Sathish Nelson | 11 Feb 07:41 2015
Picon

slow query

am connecting three tables in query. one table have 73000 records another two tables have 138000 records. but its take 12 sec for show 12402 rows in tables Tables Structure: Items Table CREATE TABLE "C_SAM_Master".items ( itemno integer NOT NULL, itemname character varying(250) NOT NULL, itemcode character varying(250) NOT NULL, shortname character varying(20) NOT NULL, aliasname character varying(250) NOT NULL, aliasnamelanguage character varying(250) NOT NULL, masteritemno integer NOT NULL, groupno1 smallint NOT NULL, groupno2 smallint NOT NULL, groupno3 smallint NOT NULL, commodityno smallint NOT NULL, unitno smallint NOT NULL, weighttype character(1) NOT NULL, altunitno smallint NOT NULL, weight double precision NOT NULL, reqmrp character(1) NOT NULL, reqbatch character(1) NOT NULL, reqmfrdate character(1) NOT NULL, mfrdateformat character varying(20) NOT NULL, reqexpdate character(1) NOT NULL, expdateformat character varying(20) NOT NULL, expdays1 smallint NOT NULL, expdays2 character(1) NOT NULL, expinfodays smallint NOT NULL, stdsaleratemethod smallint NOT NULL, salesrateper smallint NOT NULL, stdprofit1 double precision NOT NULL, stdprofit2 character(1) NOT NULL, includestockrep character(1) NOT NULL, minstock double precision NOT NULL, minstockunit smallint NOT NULL, minsaleqtynos double precision NOT NULL, minsaleqtyunit smallint NOT NULL, minsaleqty double precision NOT NULL, description text NOT NULL, remarks character varying(250) NOT NULL, actpurchaseorder character(1) NOT NULL, actpurchase character(1) NOT NULL, actpurchasereturn character(1) NOT NULL, actsalesorder character(1) NOT NULL, actsales character(1) NOT NULL, actsalesreturn character(1) NOT NULL, actreceiptnote character(1) NOT NULL, actdeliverynote character(1) NOT NULL, actconsumption character(1) NOT NULL, actproduction character(1) NOT NULL, actestimate character(1) NOT NULL, notifypurchaseorder character varying(250) NOT NULL, notifypurchase character varying(250) NOT NULL, notifypurchasereturn character varying(250) NOT NULL, notifysalesorder character varying(250) NOT NULL, notifysales character varying(250) NOT NULL, notifysalesreturn character varying(250) NOT NULL, notifyreceiptnote character varying(250) NOT NULL, notifydeliverynote character varying(250) NOT NULL, notifyconsumption character varying(250) NOT NULL, notifyproduction character varying(250) NOT NULL, notifyestimate character varying(250) NOT NULL, act boolean NOT NULL, recordowner smallint NOT NULL, lastmodified smallint NOT NULL, crdate timestamp without time zone NOT NULL, stdmaxprofit double precision NOT NULL, commodityname character varying(100) NOT NULL, lst double precision NOT NULL, unittype character(1) NOT NULL, unit1 character varying(15) NOT NULL, unit2 character varying(15) NOT NULL, units integer NOT NULL, unitname character varying(50) NOT NULL, decimals smallint NOT NULL, groupname1 character varying(50) NOT NULL, groupname2 character varying(50) NOT NULL, groupname3 character varying(50) NOT NULL, repgroupname character varying(160) NOT NULL, masteritemname character varying(100) NOT NULL, altunit1 character varying(15) NOT NULL, altunit2 character varying(15) NOT NULL, altunits integer NOT NULL, altunitname character varying(50) NOT NULL, altunitdecimals smallint NOT NULL, CONSTRAINT items_itemno_pk PRIMARY KEY (itemno), CONSTRAINT items_altunitno_fk FOREIGN KEY (altunitno) REFERENCES "C_SAM_Master".measureunits (unitno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_commodityno_fk FOREIGN KEY (commodityno) REFERENCES "C_SAM_Master".commodity (commodityno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno1_fk FOREIGN KEY (groupno1) REFERENCES "C_SAM_Master".itemgroup1 (groupno1) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno2_fk FOREIGN KEY (groupno2) REFERENCES "C_SAM_Master".itemgroup2 (groupno2) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno3_fk FOREIGN KEY (groupno3) REFERENCES "C_SAM_Master".itemgroup3 (groupno3) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_lastmodified_fk FOREIGN KEY (lastmodified) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_masteritemno_fk FOREIGN KEY (masteritemno) REFERENCES "C_SAM_Master".masteritems (masteritemno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_recordowner_fk FOREIGN KEY (recordowner) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_unitno_fk FOREIGN KEY (unitno) REFERENCES "C_SAM_Master".measureunits (unitno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_actconsumption_ck CHECK (actconsumption::text <> ''::text), CONSTRAINT items_actdeliverynote_ck CHECK (actdeliverynote::text <> ''::text), CONSTRAINT items_actestimate_ck CHECK (actestimate::text <> ''::text), CONSTRAINT items_actproduction_ck CHECK (actproduction::text <> ''::text), CONSTRAINT items_actpurchase_ck CHECK (actpurchase::text <> ''::text), CONSTRAINT items_actpurchaseorder_ck CHECK (actpurchaseorder::text <> ''::text), CONSTRAINT items_actpurchasereturn_ck CHECK (actpurchasereturn::text <> ''::text), CONSTRAINT items_actreceiptnote_ck CHECK (actreceiptnote::text <> ''::text), CONSTRAINT items_actsales_ck CHECK (actsales::text <> ''::text), CONSTRAINT items_actsalesorder_ck CHECK (actsalesorder::text <> ''::text), CONSTRAINT items_actsalesreturn_ck CHECK (actsalesreturn::text <> ''::text), CONSTRAINT items_aliasname_ck CHECK (aliasname::text <> ''::text), CONSTRAINT items_altunitdecimals_ck CHECK (altunitdecimals >= 0 AND altunitdecimals <= 3), CONSTRAINT items_altunits_ck CHECK (altunits >= 0), CONSTRAINT items_commodityname_ck CHECK (commodityname::text <> ''::text), CONSTRAINT items_decimals_ck CHECK (decimals >= 0 AND decimals <= 3), CONSTRAINT items_expdays1_ck CHECK (expdays1 >= 0), CONSTRAINT items_expinfodays_ck CHECK (expinfodays >= 0), CONSTRAINT items_includestockrep_ck CHECK (includestockrep::text <> ''::text), CONSTRAINT items_itemcode_ck CHECK (itemcode::text <> ''::text), CONSTRAINT items_itemname_ck CHECK (itemname::text <> ''::text), CONSTRAINT items_itemno_ck CHECK (itemno > 0), CONSTRAINT items_lst_ck CHECK (lst >= 0::double precision), CONSTRAINT items_minsaleqty_ck CHECK (minsaleqty >= 0::double precision), CONSTRAINT items_minsaleqtynos_ck CHECK (minsaleqtynos >= 0::double precision), CONSTRAINT items_minsaleqtyunit_ck CHECK (minsaleqtyunit >= 0 AND minsaleqtyunit <= 2), CONSTRAINT items_minstock_ck CHECK (minstock >= 0::double precision), CONSTRAINT items_minstockunit_ck CHECK (minstockunit >= 0 AND minstockunit <= 2), CONSTRAINT items_reqbatch_ck CHECK (reqbatch::text <> ''::text), CONSTRAINT items_reqexpdate_ck CHECK (reqexpdate::text <> ''::text), CONSTRAINT items_reqmfrdate_ck CHECK (reqmfrdate::text <> ''::text), CONSTRAINT items_reqmrp_ck CHECK (reqmrp::text <> ''::text), CONSTRAINT items_salesrateper_ck CHECK (salesrateper >= 0 AND salesrateper <= 4), CONSTRAINT items_stdsaleratemethod_ck CHECK (stdsaleratemethod >= 0 AND stdsaleratemethod <= 2), CONSTRAINT items_units_ck CHECK (units >= 0), CONSTRAINT items_unittype_ck CHECK (unittype::text <> ''::text), CONSTRAINT items_weight_ck CHECK (weight >= 0::double precision), CONSTRAINT items_weighttype_ck CHECK (weighttype::text <> ''::text) ) WITH ( OIDS=FALSE ) TABLESPACE "gpro2_SAM"; ALTER TABLE "C_SAM_Master".items OWNER TO gpro2user; -- Index: "C_SAM_Master".items_itemname_uq -- DROP INDEX "C_SAM_Master".items_itemname_uq; CREATE UNIQUE INDEX items_itemname_uq ON "C_SAM_Master".items USING btree (lower(itemname::text) COLLATE pg_catalog."default"); -- Rule: rule_del_items ON "C_SAM_Master".items -- DROP RULE rule_del_items ON "C_SAM_Master".items; CREATE OR REPLACE RULE rule_del_items AS ON DELETE TO "C_SAM_Master".items DO ( DELETE FROM "C_SAM_Master".itembarcode WHERE itembarcode.itemno = old.itemno; DELETE FROM "C_SAM_Master".pricelist WHERE pricelist.itemno = old.itemno; DELETE FROM "C_SAM_Master".pricelistreview WHERE pricelistreview.itemno = old.itemno; ); -- Rule: rule_del_items_c_sam_2014_2015 ON "C_SAM_Master".items -- DROP RULE rule_del_items_c_sam_2014_2015 ON "C_SAM_Master".items; CREATE OR REPLACE RULE rule_del_items_c_sam_2014_2015 AS ON DELETE TO "C_SAM_Master".items DO ( DELETE FROM "C_SAM_2014-2015".openingstock WHERE openingstock.itemno = old.itemno; DELETE FROM "C_SAM_2014-2015".stock WHERE stock.itemno = old.itemno; DELETE FROM "C_SAM_2014-2015".packingsetup WHERE packingsetup.primeitemno = old.itemno; DELETE FROM "C_SAM_2014-2015".packingsetup WHERE packingsetup.packingitemno = old.itemno; DELETE FROM "C_SAM_2014-2015".itemsuppliers WHERE itemsuppliers.itemno = old.itemno; DELETE FROM "C_SAM_2014-2015".partyopeningstock WHERE partyopeningstock.itemno = old.itemno; DELETE FROM "C_SAM_2014-2015".partystock WHERE partystock.itemno = old.itemno; ); Sales 1 Table CREATE TABLE "C_KA_2014-2015".sales1 ( vtno smallint NOT NULL, prefix character varying(5) NOT NULL, idno integer NOT NULL, suffix character varying(5) NOT NULL, txno character varying(20) NOT NULL, txdate timestamp without time zone NOT NULL, dracno integer NOT NULL, partyname character varying(100) NOT NULL, address1 character varying(100) NOT NULL, address2 character varying(100) NOT NULL, city character varying(50) NOT NULL, partytin character varying(30) NOT NULL, partycstno character varying(30) NOT NULL, mobileno character varying(15) NOT NULL, ponos character varying NOT NULL, pricelevelno smallint NOT NULL, invno character varying(20) NOT NULL, duedays smallint NOT NULL, duedate timestamp without time zone NOT NULL, paymentmode character varying(10) NOT NULL, bankrefno character varying(30) NOT NULL, bankrefdate character varying(10) NOT NULL, bankfavourname character varying(100) NOT NULL, bankcrossref character(1) NOT NULL, bankremarks character varying(100) NOT NULL, bankdate character varying(10) NOT NULL, bankstatus character(1) NOT NULL, bankreconcildate character varying(10) NOT NULL, stockpointno smallint NOT NULL, nettotal double precision NOT NULL, grosswt integer NOT NULL, tarewt integer NOT NULL, actualwt double precision NOT NULL, againstform character varying(15) NOT NULL, formseriesno character varying(15) NOT NULL, formno character varying(15) NOT NULL, formdate character varying(10) NOT NULL, totalqty double precision NOT NULL, totalqtyunit character varying(15) NOT NULL, totalfreeqty double precision NOT NULL, totalfreeqtyunit character varying(15) NOT NULL, totalaltqty double precision NOT NULL, totalaltqtyunit character varying(15) NOT NULL, orderby smallint NOT NULL, collectionby smallint NOT NULL, deliveredby1 character varying(30) NOT NULL, deliveredby2 character varying(50) NOT NULL, deliveredrefno character varying(30) NOT NULL, deliveredrefdate character varying(10) NOT NULL, goodsdelivered character(1) NOT NULL, deliveredto1 character varying(50) NOT NULL, deliveredto2 character varying(50) NOT NULL, cashrcvd double precision NOT NULL, remarks character varying(250) NOT NULL, totalstockvalue double precision NOT NULL, profit1 double precision NOT NULL, act boolean NOT NULL, totalassesvalue double precision NOT NULL, totaltax double precision NOT NULL, recordowner smallint NOT NULL, lastmodified smallint NOT NULL, crdate timestamp without time zone NOT NULL, lessadv double precision NOT NULL, lessadvpartyacno integer NOT NULL, rateadj double precision NOT NULL, jobcardtxno character varying(40) NOT NULL, txtime character varying(8) NOT NULL, CONSTRAINT sales1_txno_pk PRIMARY KEY (txno), CONSTRAINT sales1_collectionby_fk FOREIGN KEY (collectionby) REFERENCES "G_KUMARANGROUPS_Master".employee (empno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_dracno_fk FOREIGN KEY (dracno) REFERENCES "C_KA_AcMaster".acledger (acno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_lastmodified_fk FOREIGN KEY (lastmodified) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_orderby_fk FOREIGN KEY (orderby) REFERENCES "G_KUMARANGROUPS_Master".employee (empno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_pricelevelno_fk FOREIGN KEY (pricelevelno) REFERENCES "C_KA_AcMaster".acpricelevel (pricelevelno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_recordowner_fk FOREIGN KEY (recordowner) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_vto_fk FOREIGN KEY (vtno) REFERENCES "C_KA_2014-2015".acvouchertype (vtno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales1_vtnoprefixidnosuffix_uq UNIQUE (vtno, prefix, idno, suffix), CONSTRAINT sales1_duedays_ck CHECK (duedays >= 0), CONSTRAINT sales1_idno_ck CHECK (idno > 0), CONSTRAINT sales1_lessadv_ck CHECK (lessadv >= 0::double precision), CONSTRAINT sales1_lessadvpartyacno_ck CHECK (lessadvpartyacno >= 0), CONSTRAINT sales1_partyname_ck CHECK (partyname::text <> ''::text), CONSTRAINT sales1_paymentmode_ck CHECK (paymentmode::text <> ''::text), CONSTRAINT sales1_stockpointno_ck CHECK (stockpointno >= 0) ) WITH ( OIDS=FALSE ) TABLESPACE "gpro2_KA"; ALTER TABLE "C_KA_2014-2015".sales1 OWNER TO gpro2user; -- Index: "C_KA_2014-2015".sales1_acno -- DROP INDEX "C_KA_2014-2015".sales1_acno; CREATE INDEX sales1_acno ON "C_KA_2014-2015".sales1 USING btree (dracno); -- Index: "C_KA_2014-2015".sales1_txdate -- DROP INDEX "C_KA_2014-2015".sales1_txdate; CREATE INDEX sales1_txdate ON "C_KA_2014-2015".sales1 USING btree (txdate); -- Rule: rule_del_sales ON "C_KA_2014-2015".sales1 -- DROP RULE rule_del_sales ON "C_KA_2014-2015".sales1; CREATE OR REPLACE RULE rule_del_sales AS ON DELETE TO "C_KA_2014-2015".sales1 DO ( DELETE FROM "C_KA_2014-2015".packingitemsautopost WHERE packingitemsautopost.transtype::text = 'Sales'::text AND packingitemsautopost.txno::text = old.txno::text; DELETE FROM "C_KA_2014-2015".sales6 WHERE sales6.txno::text = old.txno::text; DELETE FROM "C_KA_2014-2015".sales5 WHERE sales5.txno::text = old.txno::text; DELETE FROM "C_KA_2014-2015".sales4 WHERE sales4.txno::text = old.txno::text; DELETE FROM "C_KA_2014-2015".sales3 WHERE sales3.txno::text = old.txno::text; DELETE FROM "C_KA_2014-2015".sales2 WHERE sales2.txno::text = old.txno::text; ); -- Trigger: trg_sales1 on "C_KA_2014-2015".sales1 -- DROP TRIGGER trg_sales1 ON "C_KA_2014-2015".sales1; CREATE TRIGGER trg_sales1 AFTER UPDATE OF act ON "C_KA_2014-2015".sales1 FOR EACH ROW EXECUTE PROCEDURE fn_trg_sales('sales1'); -- Trigger: trg_sales1acpost on "C_KA_2014-2015".sales1 -- DROP TRIGGER trg_sales1acpost ON "C_KA_2014-2015".sales1; CREATE TRIGGER trg_sales1acpost AFTER INSERT OR UPDATE OF txdate OR DELETE ON "C_KA_2014-2015".sales1 FOR EACH ROW EXECUTE PROCEDURE fn_trg_sales1acpost(); Sales 2 Table CREATE TABLE "C_KA_2014-2015".sales2 ( txno character varying(20) NOT NULL, slno smallint NOT NULL, itemno integer NOT NULL, rowkey smallint NOT NULL, mrp double precision NOT NULL, batchno character varying(20) NOT NULL, expdate character varying(10) NOT NULL, qty1 double precision NOT NULL, qty2 double precision NOT NULL, freeqty1 double precision NOT NULL, freeqty2 double precision NOT NULL, altqty1 double precision NOT NULL, altqty2 double precision NOT NULL, rate double precision NOT NULL, rateper smallint NOT NULL, basedvalue double precision NOT NULL, tradedis1 double precision NOT NULL, tradedis2 double precision NOT NULL, totaltradis double precision NOT NULL, adnldis1 double precision NOT NULL, adnldis2 double precision NOT NULL, totaladnldis double precision NOT NULL, adnlcostbeforevat double precision NOT NULL, assesvalue double precision NOT NULL, cst1 double precision NOT NULL, cst2 double precision NOT NULL, lst1 double precision NOT NULL, lst2 double precision NOT NULL, amount double precision NOT NULL, itemdescription text NOT NULL, adnlcostafterevat double precision NOT NULL, nsr double precision NOT NULL, totalqty double precision NOT NULL, totalfreeqty double precision NOT NULL, totalaltqty double precision NOT NULL, primaryacno integer NOT NULL, taxacno integer NOT NULL, itemstockvalue double precision NOT NULL, itemprofit1 double precision NOT NULL, cliamscheme character(1) NOT NULL, netrate double precision NOT NULL, pricelistrate double precision NOT NULL, CONSTRAINT sales2_txnoslno_pk PRIMARY KEY (txno, slno), CONSTRAINT sales2_itemno_fk FOREIGN KEY (itemno) REFERENCES "G_KUMARANGROUPS_Master".items (itemno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales2_txno_fk FOREIGN KEY (txno) REFERENCES "C_KA_2014-2015".sales1 (txno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT sales2_rowkey_uq UNIQUE (rowkey, txno), CONSTRAINT sales2_cst1_ck CHECK (cst1 >= 0::double precision), CONSTRAINT sales2_lst1_ck CHECK (lst1 >= 0::double precision), CONSTRAINT sales2_mrp_ck CHECK (mrp >= 0::double precision), CONSTRAINT sales2_netrate_ck CHECK (netrate >= 0::double precision), CONSTRAINT sales2_nsr_ck CHECK (nsr >= 0::double precision), CONSTRAINT sales2_pricelistrate_ck CHECK (pricelistrate >= 0::double precision), CONSTRAINT sales2_primaryacno_ck CHECK (primaryacno >= 0), CONSTRAINT sales2_rate_ck CHECK (rate >= 0::double precision), CONSTRAINT sales2_rateper_ck CHECK (rateper >= 0 AND rateper <= 4), CONSTRAINT sales2_rowkey_ck CHECK (rowkey > 0), CONSTRAINT sales2_slno_ck CHECK (slno > 0), CONSTRAINT sales2_taxacno_ck CHECK (taxacno >= 0), CONSTRAINT sales2_totalfreeqty_ck CHECK ((totalqty + totalfreeqty) <> 0::double precision), CONSTRAINT sales2_totalqty_ck CHECK ((totalqty + totalfreeqty) <> 0::double precision) ) WITH ( OIDS=FALSE ) TABLESPACE "gpro2_KA"; ALTER TABLE "C_KA_2014-2015".sales2 OWNER TO gpro2user; -- Index: "C_KA_2014-2015".sales2_itemno -- DROP INDEX "C_KA_2014-2015".sales2_itemno; CREATE INDEX sales2_itemno ON "C_KA_2014-2015".sales2 USING btree (itemno); -- Index: "C_KA_2014-2015".sales2_txno -- DROP INDEX "C_KA_2014-2015".sales2_txno; CREATE INDEX sales2_txno ON "C_KA_2014-2015".sales2 USING btree (txno COLLATE pg_catalog."default"); -- Trigger: trg_sales2 on "C_KA_2014-2015".sales2 -- DROP TRIGGER trg_sales2 ON "C_KA_2014-2015".sales2; CREATE TRIGGER trg_sales2 AFTER INSERT OR DELETE ON "C_KA_2014-2015".sales2 FOR EACH ROW EXECUTE PROCEDURE fn_trg_sales2(); Query: select grp,disp,alisdisp,ord,'' as adnlorder,'' as calcorder,sum(case when ord =3 then qty end) as qty,sum(case when ord=3 then freeqty end) as freeqty,max(case when ord=3 then unit1 end) as unit1,sum(altqty) as altqty,max(altunit1) as altunit1,sum(discount) as discount,sum(amount) as amount,sum(itemprofit) as itemprofit,0.00 as profitper,sum(itemstockvalue) as itemstockvalue from (select unnest(array[repgroupname,repgroupname||'-'||masteritemname,repgroupname||'-'||masteritemname||'-'||itemname]) as grp,unnest(array[case when repgroupname ='' then 'UnGrouped' else repgroupname end,masteritemname,itemname]) as disp,unnest(array['','',aliasnamelanguage]) as alisdisp,unnest(array[1,2,3]) as ord,cast(case when units > 1 then cast(case when sum(qty) > 0 then floor(sum(qty)/units) else ceil(sum(qty)/units) end as text) when (mod(cast(sum(qty) as integer),units))<>0 then '.' || abs(cast(mod(cast(sum(qty) as integer),units) as integer)) else cast(sum(qty) as text) end as double precision) as qty,cast(case when units > 1 then cast(case when sum(freeqty) > 0 then floor(sum(freeqty)/units) else ceil(sum(freeqty)/units) end as text) when (mod(cast(sum(freeqty) as integer),units))<>0 then '.' || abs(cast(mod(cast(sum(freeqty) as integer),units) as integer)) else cast(sum(freeqty) as text) end as double precision) as freeqty,unit1,cast(case when altunits > 1 then cast(case when sum(altqty) > 0 then floor(sum(altqty)/altunits) else ceil(sum(altqty)/altunits) end as text) when (mod(cast(sum(altqty) as integer),altunits))<>0 then '.' || abs(cast(mod(cast(sum(altqty) as integer),altunits) as integer)) else cast(sum(altqty) as text) end as double precision) as altqty,altunit1,sum(discount) as discount,sum(itemstockvalue) as itemstockvalue,sum(itemprofit) as itemprofit,sum(amount) as amount,shortname from (select i.repgroupname,i.aliasnamelanguage,i.masteritemname,i.itemname,i.groupname1,i.groupname2,i.groupname3,i.units,i.unit1,i.unit2,i.altunit1,i.altunit2,i.altunits,sum(s2.totalqty) as qty,sum(s2.totalfreeqty) as freeqty,sum(s2.totalaltqty) as altqty,sum(s2.totaltradis + totaladnldis) as discount,sum(itemstockvalue) as itemstockvalue,sum(itemprofit1) as itemprofit,sum(s2.amount) as amount,'KA'::text as shortname from "C_KA_2014-2015".sales1 s1 inner join "C_KA_2014-2015".sales2 s2 on s1.txno=s2.txno inner join "G_KUMARANGROUPS_Master".items i on i.itemno=s2.itemno where s1.act='t' and s1.txdate >= '01/04/2014' and s1.txdate <= '30/01/2015'group by i.repgroupname,i.aliasnamelanguage,i.groupname1,i.groupname2,i.groupname3,i.units,i.unit1,i.unit2,i.altunit1,i.altunit2,i.altunits,i.itemname,i.masteritemname ) as tt group by grp,disp,alisdisp,units,altunits,ord,shortname,unit1,altunit1 order by grp,disp ) as tab where disp <> '' group by grp,disp,alisdisp,ord order by grp,disp Explain Analysis and Buffers "GroupAggregate (cost=3586024.69..3617755.12 rows=72944 width=160) (actual time=11819.837..11884.868 rows=12064 loops=1)" " Buffers: shared hit=4462 read=9825, temp read=6381 written=6361" " -> Sort (cost=3586024.69..3587848.28 rows=729435 width=160) (actual time=11819.780..11831.894 rows=12068 loops=1)" " Sort Key: tab.grp, tab.disp, tab.alisdisp, tab.ord" " Sort Method: external sort Disk: 1336kB" " Buffers: shared hit=4462 read=9825, temp read=6381 written=6361" " -> Subquery Scan on tab (cost=2742202.68..3342958.78 rows=729435 width=160) (actual time=11424.007..11727.170 rows=12068 loops=1)" " Filter: ((tab.disp)::text <> ''::text)" " Rows Removed by Filter: 7" " Buffers: shared hit=4462 read=9825, temp read=6214 written=6194" " -> GroupAggregate (cost=2742202.68..3333795.03 rows=733100 width=115) (actual time=11424.001..11703.904 rows=12075 loops=1)" " Buffers: shared hit=4462 read=9825, temp read=6214 written=6194" " -> Sort (cost=2742202.68..2760528.43 rows=7330300 width=115) (actual time=11423.951..11543.478 rows=36183 loops=1)" " Sort Key: (unnest(ARRAY[tt.repgroupname, ((((tt.repgroupname)::text || '-'::text) || (tt.masteritemname)::text))::character varying, ((((((tt.repgroupname)::text || '-'::text) || (tt.masteritemname)::text) || '-'::text) || (tt.ite (...)" " Sort Method: external merge Disk: 3552kB" " Buffers: shared hit=4462 read=9825, temp read=6214 written=6194" " -> Subquery Scan on tt (cost=56047.61..102407.06 rows=7330300 width=115) (actual time=8877.785..11023.746 rows=36183 loops=1)" " Buffers: shared hit=4462 read=9825, temp read=5768 written=5748" " -> GroupAggregate (cost=56047.61..63373.22 rows=73303 width=96) (actual time=8877.762..10906.503 rows=12061 loops=1)" " Buffers: shared hit=4462 read=9825, temp read=5768 written=5748" " -> Sort (cost=56047.61..56347.27 rows=119865 width=96) (actual time=8877.576..10555.267 rows=119714 loops=1)" " Sort Key: i.repgroupname, i.aliasnamelanguage, i.groupname1, i.groupname2, i.groupname3, i.units, i.unit1, i.unit2, i.altunit1, i.altunit2, i.altunits, i.itemname, i.masteritemname" " Sort Method: external merge Disk: 12432kB" " Buffers: shared hit=4462 read=9825, temp read=5768 written=5748" " -> Hash Join (cost=13948.80..33644.37 rows=119865 width=96) (actual time=617.917..1756.039 rows=119714 loops=1)" " Hash Cond: (s2.itemno = i.itemno)" " Buffers: shared hit=4462 read=9825, temp read=3098 written=3078" " -> Hash Join (cost=8849.48..23064.41 rows=119865 width=68) (actual time=339.948..1054.380 rows=119714 loops=1)" " Hash Cond: ((s2.txno)::text = (s1.txno)::text)" " Buffers: shared hit=1585 read=9825, temp read=1539 written=1533" " -> Seq Scan on sales2 s2 (cost=0.00..7490.64 rows=144964 width=76) (actual time=0.023..262.043 rows=144964 loops=1)" " Buffers: shared hit=814 read=5227" " -> Hash (cost=7196.35..7196.35 rows=100731 width=8) (actual time=339.873..339.873 rows=100850 loops=1)" " Buckets: 4096 Batches: 4 Memory Usage: 803kB" " Buffers: shared hit=771 read=4598, temp written=257" " -> Seq Scan on sales1 s1 (cost=0.00..7196.35 rows=100731 width=8) (actual time=0.029..230.250 rows=100850 loops=1)" " Filter: (act AND (txdate >= '2014-04-01 00:00:00'::timestamp without time zone) AND (txdate <= '2015-01-30 00:00:00'::timestamp without time zone))" " Rows Removed by Filter: 20973" " Buffers: shared hit=771 read=4598" " -> Hash (cost=3610.03..3610.03 rows=73303 width=36) (actual time=277.327..277.327 rows=73303 loops=1)" " Buckets: 2048 Batches: 8 Memory Usage: 593kB" " Buffers: shared hit=2877, temp written=475" " -> Seq Scan on items i (cost=0.00..3610.03 rows=73303 width=36) (actual time=0.007..153.900 rows=73303 loops=1)" " Buffers: shared hit=2877" "Total runtime: 11897.250 ms" My Hardware is InterCore CPU 3.10 CHZ 2.89 GB RAM








Gmane