Re: 7.3.1 New install, large queries are slow - Mailing list pgsql-performance

Thanks to everyone for the quick replies!  I'm sure that my lack of skill with SQL queries is the main problem.  What's
strangeto me is how MSSQL takes my bad queries and makes them look good anyway.  It must have a real smart planner.
 
 
Several changes:  shared_buffers = 131072, sort_mem = 32768, shmmax = 2097152000, shmall = 131072000.  I couldn't find
anyinfo out there on the relationship between shmmax and shmall, so I just preserved the ratio from the RedHat defaults
(1:16). As far as sort_mem goes, I expect to be running no more than 3 concurrent queries and they will all be just as
complexas this one.  Do you think sort_mem=32768 is a reasonable size?  None of these changes seemed to help speed up
thingshowever.
 
 
REINDEX INDEX batchdetail_ix_tranamount_idx;    was executed successfully, although it took 15 minutes.
ANALYZE executed in 2 minutes, even though I increased default_statistics_target = 30.  Should I increase it even more?
I don't mind the extra overhead each night if it will make my queries faster.  (Idiot check: I did actually stop and
startthe postmaster after changing all these settings).
 
 
Andrew Sullivan wrote:
>First, the performance of foreign keys is flat-out awful in Postgres.
>I suggest avoiding them if you can.

I don't have any problem getting rid of FKs, especially if it might actually help performance.  The nightly data import
iswell-defined and should always observe referential integrity, so I guess the db doesn't really need to enforce it.
InMSSQL, adding FKs was supposed to actually benefit SELECT performance.  Is it pretty much universally accepted that I
shoulddrop all my foreign keys?
 

>Second, ordering joins explicitly (with the JOIN keyword) constrains
>the planner, and may select  bad plan.  The explain analyse output
>was nice, but I didn't see the query, so I can't tell what the plan
>maybe ought to be.

I think this is the most likely problem.  I've read through Chapter 10 of the 7.3 docs, but I still don't feel like I
knowwhat would be a good order.  How do you learn this stuff anyway?  Trial and error?
 

>Third, I didn't see any suggestion that you'd moved the WAL onto its
>own disk.  That will mostly help when you are under write load;

I don't think I'm going to bother with moving the WAL....the write load during the day is very, very light (when
queriesare run).   Disk I/O is clearly not the limiting factor (yet!).  
 
 
So here's the query, and another EXPLAIN ANALYZE to go with it (executed after all setting changes).  The same result
columnsand JOINS are performed all day with variations on the WHERE clause; other possible search columns are the ones
thatare indexed (see below).  The 4 tables that use LEFT JOIN only sometimes have matching records, hence the OUTER
join.
 
EXPLAIN ANALYZE
SELECT b.batchdate, d.batchdetailid, t.bankno, d.trandate, d.tranamount, 
d.submitinterchange, d.authamount, d.authno, d.cardtypeid, d.mcccode, 
m.name AS merchantname, c.cardtype, m.merchid, 
p1.localtaxamount, p1.productidentifier, dr.avsresponse, 
cr.checkoutdate, cr.noshowindicator, ck.checkingacctno, 
ck.abaroutingno, ck.checkno 
FROM tranheader t 
INNER JOIN batchheader b ON t.tranheaderid = b.tranheaderid 
INNER JOIN merchants m ON m.merchantid = b.merchantid 
INNER JOIN batchdetail d ON d.batchid = b.batchid 
INNER JOIN cardtype c ON d.cardtypeid = c.cardtypeid 
LEFT JOIN purc1 p1 ON p1.batchdetailid = d.batchdetailid 
LEFT JOIN direct dr ON dr.batchdetailid = d.batchdetailid 
LEFT JOIN carrental cr ON cr.batchdetailid = d.batchdetailid 
LEFT JOIN checks ck ON ck.batchdetailid = d.batchdetailid 
WHERE t.clientid = 6 
AND d.tranamount BETWEEN 500.0 AND 700.0 
AND b.batchdate > '2002-12-15' 
AND m.merchid = '701252267' 
ORDER BY b.batchdate DESC 
LIMIT 50

Limit  (cost=1829972.39..1829972.39 rows=1 width=285) (actual time=1556497.79..1556497.80 rows=5 loops=1)
  ->  Sort  (cost=1829972.39..1829972.39 rows=1 width=285) (actual time=1556497.78..1556497.79 rows=5 loops=1)
        Sort Key: b.batchdate
        ->  Nested Loop  (cost=1771874.32..1829972.38 rows=1 width=285) (actual time=1538783.03..1556486.64 rows=5
loops=1)
              Join Filter: ("inner".batchdetailid = "outer".batchdetailid)
              ->  Nested Loop  (cost=1771874.32..1829915.87 rows=1 width=247) (actual time=1538760.60..1556439.67
rows=5loops=1)
 
                    Join Filter: ("inner".batchdetailid = "outer".batchdetailid)
                    ->  Nested Loop  (cost=1771874.32..1829915.86 rows=1 width=230) (actual time=1538760.55..1556439.50
rows=5loops=1)
 
                          Join Filter: ("inner".batchdetailid = "outer".batchdetailid)
                          ->  Nested Loop  (cost=1771874.32..1829915.85 rows=1 width=221) (actual
time=1538760.51..1556439.31rows=5 loops=1)
 
                                Join Filter: ("inner".batchdetailid = "outer".batchdetailid)
                                ->  Nested Loop  (cost=1771874.32..1773863.81 rows=1 width=202) (actual
time=1529153.84..1529329.65rows=5 loops=1)
 
                                      Join Filter: ("outer".cardtypeid = "inner".cardtypeid)
                                      ->  Merge Join  (cost=1771874.32..1773862.58 rows=1 width=188) (actual
time=1529142.55..1529317.99rows=5 loops=1)
 
                                            Merge Cond: ("outer".batchid = "inner".batchid)
                                            ->  Sort  (cost=116058.42..116058.43 rows=3 width=118) (actual
time=14184.11..14184.14rows=17 loops=1)
 
                                                  Sort Key: b.batchid
                                                  ->  Hash Join  (cost=109143.44..116058.39 rows=3 width=118) (actual
time=12398.29..14184.03rows=17 loops=1)
 
                                                        Hash Cond: ("outer".merchantid = "inner".merchantid)
                                                        ->  Merge Join  (cost=109137.81..114572.94 rows=295957
width=40)(actual time=12359.75..13848.67 rows=213387 loops=1)
 
                                                              Merge Cond: ("outer".tranheaderid =
"inner".tranheaderid)
                                                              ->  Index Scan using tranheader_ix_tranheaderid_idx on
tranheadert  (cost=0.00..121.15 rows=1923 width=16) (actual time=0.17..10.91 rows=1923 loops=1)
 
                                                                    Filter: (clientid = 6)
                                                              ->  Sort  (cost=109137.81..109942.73 rows=321966
width=24)(actual time=12317.83..12848.43 rows=329431 loops=1)
 
                                                                    Sort Key: b.tranheaderid
                                                                    ->  Seq Scan on batchheader b  (cost=0.00..79683.44
rows=321966width=24) (actual time=29.93..10422.75 rows=329431 loops=1)
 
                                                                          Filter: (batchdate > '2002-12-15
00:00:00'::timestampwithout time zone)
 
                                                        ->  Hash  (cost=5.63..5.63 rows=1 width=78) (actual
time=21.06..21.06rows=0 loops=1)
 
                                                              ->  Index Scan using merchants_ix_merchid_idx on
merchantsm  (cost=0.00..5.63 rows=1 width=78) (actual time=21.05..21.05 rows=1 loops=1)
 
                                                                    Index Cond: (merchid = '701252267'::character
varying)
                                            ->  Sort  (cost=1655815.90..1656810.15 rows=397698 width=70) (actual
time=1513860.73..1514497.92rows=368681 loops=1)
 
                                                  Sort Key: d.batchid
                                                  ->  Index Scan using batchdetail_ix_tranamount_idx on batchdetail d
(cost=0.00..1597522.38rows=397698 width=70) (actual time=14.05..1505397.17 rows=370307 loops=1)
 
                                                        Index Cond: ((tranamount >= 500.0) AND (tranamount <= 700.0))
                                      ->  Seq Scan on cardtype c  (cost=0.00..1.10 rows=10 width=14) (actual
time=2.25..2.28rows=10 loops=5)
 
                                ->  Seq Scan on purc1 p1  (cost=0.00..44285.35 rows=941335 width=19) (actual
time=2.40..3812.43rows=938770 loops=5)
 
                          ->  Seq Scan on direct dr  (cost=0.00..0.00 rows=1 width=9) (actual time=0.00..0.00 rows=0
loops=5)
                    ->  Seq Scan on carrental cr  (cost=0.00..0.00 rows=1 width=17) (actual time=0.00..0.00 rows=0
loops=5)
              ->  Seq Scan on checks ck  (cost=0.00..40.67 rows=1267 width=38) (actual time=0.50..7.05 rows=1267
loops=5)
Total runtime: 1556553.76 msec

 
Tomasz Myrta wrote:
>Seq Scan on batchheader b  (cost=0.00..79587.23 rows=308520 width=56)
>Can you write what condition and indexes does batchheader have?
 
batchheader has 2.6 million records:
CREATE TABLE public.batchheader (
  batchid int8 DEFAULT nextval('"batchheader_batchid_key"'::text) NOT NULL, 
  line int4, 
  tranheaderid int4, 
  merchantid int4, 
  batchdate timestamp, 
  merchref char(16), 
  carryindicator char(1), 
  assocno varchar(6), 
  merchbankno char(4), 
  debitcredit char(1), 
  achpostdate timestamp, 
  trancode char(4), 
  netdeposit numeric(18, 4), 
  CONSTRAINT batchheader_ix_batchid_idx UNIQUE (batchid), 
  CONSTRAINT batchheader_pkey PRIMARY KEY (batchid), 
  CONSTRAINT fk_bh_th FOREIGN KEY (tranheaderid) REFERENCES tranheader (tranheaderid) ON DELETE RESTRICT ON UPDATE NO
ACTIONNOT DEFERRABLE INITIALLY IMMEDIATE
 
) WITH OIDS;
CREATE UNIQUE INDEX batchheader_ix_batchid_idx ON batchheader USING btree (batchid);
CREATE INDEX batchheader_ix_batchdate_idx ON batchheader USING btree (batchdate);
CREATE INDEX batchheader_ix_merchantid_idx ON batchheader USING btree (merchantid);
CREATE INDEX batchheader_ix_merchref_idx ON batchheader USING btree (merchref);
CREATE INDEX batchheader_ix_netdeposit_idx ON batchheader USING btree (netdeposit);

And here's batchdetail too, just for kicks.  23 million records.
CREATE TABLE public.batchdetail (
  batchdetailid int8 DEFAULT nextval('public.batchdetail_batchdetailid_seq'::text) NOT NULL, 
  line int4, 
  batchid int4, 
  merchno varchar(16), 
  assocno varchar(6), 
  refnumber char(23), 
  trandate timestamp, 
  tranamount numeric(18, 4), 
  netdeposit numeric(18, 4), 
  cardnocfb bytea, 
  bestinterchange char(2), 
  submitinterchange char(2), 
  downgrader1 char(4), 
  downgrader2 char(4), 
  downgrader3_1 char(1), 
  downgrader3_2 char(1), 
  downgrader3_3 char(1), 
  downgrader3_4 char(1), 
  downgrader3_5 char(1), 
  downgrader3_6 char(1), 
  downgrader3_7 char(1), 
  onlineentry char(1), 
  achflag char(1), 
  authsource char(1), 
  cardholderidmeth char(1), 
  catindicator char(1), 
  reimbattribute char(1), 
  motoindicator char(1), 
  authcharind char(1), 
  banknetrefno char(9), 
  banknetauthdate char(6), 
  draftaflag char(1), 
  authcurrencycode char(3), 
  authamount numeric(18, 4), 
  validcode char(4), 
  authresponsecode char(2), 
  debitnetworkid char(3), 
  switchsetindicator char(1), 
  posentrymode char(2), 
  debitcredit char(1), 
  reversalflag char(1), 
  merchantname varchar(25), 
  authno char(6), 
  rejectreason char(4), 
  cardtypeid int4, 
  currencycode char(3), 
  origtranamount numeric(18, 4), 
  foreigncard char(1), 
  carryover char(1), 
  extensionrecord char(2), 
  mcccode char(4), 
  terminalid char(8), 
  submitinterchange3b char(3), 
  purchaseid varchar(25), 
  trancode char(4), 
  CONSTRAINT batchdetail_pkey PRIMARY KEY (batchdetailid)
) WITH OIDS;
CREATE INDEX batchdetail_ix_authno_idx ON batchdetail USING btree (authno);
CREATE INDEX batchdetail_ix_batchdetailid_idx ON batchdetail USING btree (batchdetailid);
CREATE INDEX batchdetail_ix_cardnocfb_idx ON batchdetail USING btree (cardnocfb);
CREATE INDEX batchdetail_ix_posentrymode_idx ON batchdetail USING btree (posentrymode);
CREATE INDEX batchdetail_ix_submitinterchange3b_idx ON batchdetail USING btree (submitinterchange3b);
CREATE INDEX batchdetail_ix_tranamount_idx ON batchdetail USING btree (tranamount);
 
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.
Sacramento, CA