Thread

Re: 7.3.1 New install, large queries are slow

Tomasz Myrta <jasiek@klaster.net> — 2003-01-01T09:59:06Z

Roman Fail wrote:
> The same result columns and JOINS are performed all day with variations on the WHERE clause;
Are there any where clauses which all of theses variation have?
If yes - query can be reordered to contain explicit joins for these clauses and 
to let Postgres to find best solution for other joins.

I know, it is not best solution, but sometimes I prefer finding best join order by myself. 
I create then several views returning the same values, but manualy ordered for specific where clauses.

Tomasz Myrta

Re: 7.3.1 New install, large queries are slow

Roman Fail <rfail@posportal.com> — 2003-01-15T23:30:55Z

Thanks to everyone for the quick replies!  I'm sure that my lack of skill with SQL queries is the main problem.  What's strange to 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 any info 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 complex as this one.  Do you think sort_mem=32768 is a reasonable size?  None of these changes seemed to help speed up things however.

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 start the 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 is well-defined and should always observe referential integrity, so I guess the db doesn't really need to enforce it.  In MSSQL, adding FKs was supposed to actually benefit SELECT performance.  Is it pretty much universally accepted that I should drop 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 know what 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 queries are 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 columns and JOINS are performed all day with variations on the WHERE clause; other possible search columns are the ones that are 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=5 loops=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=5 loops=1)
                          Join Filter: ("inner".batchdetailid = "outer".batchdetailid)
                          ->  Nested Loop  (cost=1771874.32..1829915.85 rows=1 width=221) (actual time=1538760.51..1556439.31 rows=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.65 rows=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.99 rows=5 loops=1)
                                            Merge Cond: ("outer".batchid = "inner".batchid)
                                            ->  Sort  (cost=116058.42..116058.43 rows=3 width=118) (actual time=14184.11..14184.14 rows=17 loops=1)
                                                  Sort Key: b.batchid
                                                  ->  Hash Join  (cost=109143.44..116058.39 rows=3 width=118) (actual time=12398.29..14184.03 rows=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 tranheader t  (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=321966 width=24) (actual time=29.93..10422.75 rows=329431 loops=1)
                                                                          Filter: (batchdate > '2002-12-15 00:00:00'::timestamp without time zone)
                                                        ->  Hash  (cost=5.63..5.63 rows=1 width=78) (actual time=21.06..21.06 rows=0 loops=1)
                                                              ->  Index Scan using merchants_ix_merchid_idx on merchants m  (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.92 rows=368681 loops=1)
                                                  Sort Key: d.batchid
                                                  ->  Index Scan using batchdetail_ix_tranamount_idx on batchdetail d  (cost=0.00..1597522.38 rows=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.28 rows=10 loops=5)
                                ->  Seq Scan on purc1 p1  (cost=0.00..44285.35 rows=941335 width=19) (actual time=2.40..3812.43 rows=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 ACTION NOT 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

Re: 7.3.1 New install, large queries are slow

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-01-16T03:40:04Z

>  So here's the query, and another EXPLAIN ANALYZE to go with it
> (executed after all setting changes).  The same result columns and
> JOINS are performed all day with variations on the WHERE clause; other
> possible search columns are the ones that are 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

Well, you might get a little help by replace the from with
 something like:

FROM transheader t, batchheader b, merchants m, cardtype c,
batchdetail d
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

and adding
AND t.tranheaderid=b.tranheaderid
AND m.merchantid=b.merchantid
AND d.batchid=b.batchid
AND c.cardtypeid=d.cardtypeid
to the WHERE conditions.

That should at least allow it to do some small reordering
of the joins.  I don't think that alone is going to do much,
since most of the time seems to be on the scan of d.

What does vacuum verbose batchdetail give you (it'll give
an idea of pages anyway)

Re: 7.3.1 New install, large queries are slow

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-01-16T03:46:16Z

On Wed, 15 Jan 2003, Roman Fail wrote:

> Thanks to everyone for the quick replies!  I'm sure that my lack of
> skill with SQL queries is the main problem.  What's strange to me is
> how MSSQL takes my bad queries and makes them look good anyway.  It
> must have a real smart planner.

As a followup, if you do
set enable_indexscan=off;
before running the explain analyze, what does that give you?

Re: 7.3.1 New install, large queries are slow

Tom Lane <tgl@sss.pgh.pa.us> — 2003-01-16T04:35:00Z

"Roman Fail" <rfail@posportal.com> writes:
> Thanks to everyone for the quick replies!  I'm sure that my lack of
> skill with SQL queries is the main problem.  What's strange to me is
> how MSSQL takes my bad queries and makes them look good anyway.  It
> must have a real smart planner.

I think more likely the issue is that your use of JOIN syntax is forcing
Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
significance to the use of "a JOIN b" syntax as opposed to "FROM a, b"
syntax.  Postgres does.  Whether this is a bug or a feature depends on
your point of view --- but there are folks out there who find it to be
a life-saver.  You can find some explanations at
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html

> Is it pretty much universally accepted that I should drop all my
> foreign keys?

No.  They don't have any effect on SELECT performance in Postgres.
They will impact update speed, but that's not your complaint (at the
moment).  Don't throw away data integrity protection until you know
you need to.

			regards, tom lane

Re: 7.3.1 New install, large queries are slow

Josh Berkus <josh@agliodbs.com> — 2003-01-16T04:41:11Z

Tom, Roman,

> I think more likely the issue is that your use of JOIN syntax is
> forcing
> Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
> significance to the use of "a JOIN b" syntax as opposed to "FROM a,
> b"
> syntax.  

That's correct.  MSSQL will reorder equijoins, even when explicitly
declared.

Hey, Roman, how many records in BatchDetail, anyway?

Josh Berkus

Re: 7.3.1 New install, large queries are slow

Kevin Brown <kevin@sysexperts.com> — 2003-01-16T04:48:47Z

Tom Lane wrote:
> "Roman Fail" <rfail@posportal.com> writes:
> > Thanks to everyone for the quick replies!  I'm sure that my lack of
> > skill with SQL queries is the main problem.  What's strange to me is
> > how MSSQL takes my bad queries and makes them look good anyway.  It
> > must have a real smart planner.
> 
> I think more likely the issue is that your use of JOIN syntax is forcing
> Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
> significance to the use of "a JOIN b" syntax as opposed to "FROM a, b"
> syntax.  Postgres does.  Whether this is a bug or a feature depends on
> your point of view --- but there are folks out there who find it to be
> a life-saver. 

Since it *does* depend on one's point of view, would it be possible to
have control over this implemented in a session-defined variable (with
the default in the GUC, of course)?  I wouldn't be surprised if a lot
of people get bitten by this.


-- 
Kevin Brown					      kevin@sysexperts.com

Re: 7.3.1 New install, large queries are slow

Tom Lane <tgl@sss.pgh.pa.us> — 2003-01-16T05:07:52Z

Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> ... Whether this is a bug or a feature depends on
>> your point of view --- but there are folks out there who find it to be
>> a life-saver. 

> Since it *does* depend on one's point of view, would it be possible to
> have control over this implemented in a session-defined variable (with
> the default in the GUC, of course)?

I have no objection to doing that --- anyone care to contribute code to
make it happen?  (I think the trick would be to fold plain-JOIN jointree
entries into FROM-list items in planner.c, somewhere near the code that
hoists sub-SELECTs into the main join tree.  But I haven't tried it, and
have no time to in the near future.)

			regards, tom lane

Re: 7.3.1 New install, large queries are slow

Kevin Brown <kevin@sysexperts.com> — 2003-01-16T10:40:25Z

Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> > Tom Lane wrote:
> >> ... Whether this is a bug or a feature depends on
> >> your point of view --- but there are folks out there who find it to be
> >> a life-saver. 
> 
> > Since it *does* depend on one's point of view, would it be possible to
> > have control over this implemented in a session-defined variable (with
> > the default in the GUC, of course)?
> 
> I have no objection to doing that --- anyone care to contribute code to
> make it happen?  (I think the trick would be to fold plain-JOIN jointree
> entries into FROM-list items in planner.c, somewhere near the code that
> hoists sub-SELECTs into the main join tree.  But I haven't tried it, and
> have no time to in the near future.)

I'm looking at the code now (the 7.2.3 code in particular, but I
suspect for this purpose the code is likely to be very similar to the
CVS tip), but it's all completely new to me and the developer
documentation isn't very revealing of the internals.  The optimizer
code (I've been looking especially at make_jointree_rel() and
make_fromexpr_rel()) looks a bit tricky...it'll take me some time to
completely wrap my brain around it.  Any pointers to revealing
documentation would be quite helpful!

-- 
Kevin Brown					      kevin@sysexperts.com

Re: 7.3.1 New install, large queries are slow

Hannu Krosing <hannu@tm.ee> — 2003-01-16T12:54:58Z

On Thu, 2003-01-16 at 03:40, Stephan Szabo wrote:
> >  So here's the query, and another EXPLAIN ANALYZE to go with it
> > (executed after all setting changes).  The same result columns and
> > JOINS are performed all day with variations on the WHERE clause; other
> > possible search columns are the ones that are 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

How much of data in d has tranamount BETWEEN 500.0 AND 700.0 ?

Do you have an index on d.tranamount ?

> > AND b.batchdate > '2002-12-15'

again - how much of b.batchdate > '2002-12-15' ?

is there an index

> > AND m.merchid = '701252267'

ditto

> > ORDER BY b.batchdate DESC
> > LIMIT 50

these two together make me think that perhaps 

b.batchdate between  '2003-12-12' and '2002-12-15' 

could be better at making the optimiser see that reverse index scan on
b.batchdate would be the way to go.

> Well, you might get a little help by replace the from with

-- 
Hannu Krosing <hannu@tm.ee>

Re: 7.3.1 New install, large queries are slow

Andrew Sullivan <andrew@libertyrms.info> — 2003-01-16T13:17:38Z

On Wed, Jan 15, 2003 at 03:30:55PM -0800, Roman Fail wrote:
> Thanks to everyone for the quick replies!  I'm sure that my lack of skill with SQL queries is the main problem.  What's strange to me is how MSSQL takes my bad queries and makes them look good anyway.  It must have a real smart planner.
> 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 is well-defined

Sorry, I think I sent this too quickly.  FKs make no difference to
SELECT performance, so if you're not doing updates and the like at
the same time as the SELECTs, there's no advantage.  So you should
leave the FKs in place.

> 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 know what would
> be a good order.  How do you learn this stuff anyway?  Trial and
> error?

Sorry, but yes.

A
-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: 7.3.1 New install, large queries are slow

Tom Lane <tgl@sss.pgh.pa.us> — 2003-01-16T15:46:22Z

Kevin Brown <kevin@sysexperts.com> writes:
> I'm looking at the code now (the 7.2.3 code in particular, but I
> suspect for this purpose the code is likely to be very similar to the
> CVS tip), but it's all completely new to me and the developer
> documentation isn't very revealing of the internals.  The optimizer
> code (I've been looking especially at make_jointree_rel() and
> make_fromexpr_rel()) looks a bit tricky...it'll take me some time to
> completely wrap my brain around it.  Any pointers to revealing
> documentation would be quite helpful!

src/backend/optimizer/README is a good place to start.

I'd recommend working with CVS tip; there is little point in doing any
nontrivial development in the 7.2 branch.  You'd have to port it forward
anyway.

			regards, tom lane

Re: 7.3.1 New install, large queries are slow

Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-16T15:53:08Z

I was surprised to hear that JOIN syntax constrained the planner.  We 
have a policy of using JOIN syntax to describe the table relationships 
and where clauses to describe the selection process for our queries.  It 
was our understanding that the JOIN syntax was introduced to support 
this approach, but not to contrain the planner.  

Is there any way to sell the planner to consider JOIN syntax as 
equivalent to WHERE clauses and to not use them to force the planner 
down a specific path?  Can we get that added as an option (and then made 
available to use JDBC folks as a URL parameter).  It would make my team 
very happy :-).

I think that making this an option will help all those migrating to 
Postgres who did not expect that JOINs forced the planner down specific 
plans.    Is  it possible/reasonable to add?

Charlie

Tom Lane wrote:

>"Roman Fail" <rfail@posportal.com> writes:
>  
>
>>Thanks to everyone for the quick replies!  I'm sure that my lack of
>>skill with SQL queries is the main problem.  What's strange to me is
>>how MSSQL takes my bad queries and makes them look good anyway.  It
>>must have a real smart planner.
>>    
>>
>
>I think more likely the issue is that your use of JOIN syntax is forcing
>Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
>significance to the use of "a JOIN b" syntax as opposed to "FROM a, b"
>syntax.  Postgres does.  Whether this is a bug or a feature depends on
>your point of view --- but there are folks out there who find it to be
>a life-saver.  You can find some explanations at
>http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html
>
>  
>
>>Is it pretty much universally accepted that I should drop all my
>>foreign keys?
>>    
>>
>
>No.  They don't have any effect on SELECT performance in Postgres.
>They will impact update speed, but that's not your complaint (at the
>moment).  Don't throw away data integrity protection until you know
>you need to.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>  
>

-- 

Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Re: 7.3.1 New install, large queries are slow

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-01-16T16:18:35Z

Is this a TODO item?

---------------------------------------------------------------------------

Charles H. Woloszynski wrote:
> I was surprised to hear that JOIN syntax constrained the planner.  We 
> have a policy of using JOIN syntax to describe the table relationships 
> and where clauses to describe the selection process for our queries.  It 
> was our understanding that the JOIN syntax was introduced to support 
> this approach, but not to contrain the planner.  
> 
> Is there any way to sell the planner to consider JOIN syntax as 
> equivalent to WHERE clauses and to not use them to force the planner 
> down a specific path?  Can we get that added as an option (and then made 
> available to use JDBC folks as a URL parameter).  It would make my team 
> very happy :-).
> 
> 
> I think that making this an option will help all those migrating to 
> Postgres who did not expect that JOINs forced the planner down specific 
> plans.    Is  it possible/reasonable to add?
> 
> Charlie
> 
> 
> Tom Lane wrote:
> 
> >"Roman Fail" <rfail@posportal.com> writes:
> >  
> >
> >>Thanks to everyone for the quick replies!  I'm sure that my lack of
> >>skill with SQL queries is the main problem.  What's strange to me is
> >>how MSSQL takes my bad queries and makes them look good anyway.  It
> >>must have a real smart planner.
> >>    
> >>
> >
> >I think more likely the issue is that your use of JOIN syntax is forcing
> >Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
> >significance to the use of "a JOIN b" syntax as opposed to "FROM a, b"
> >syntax.  Postgres does.  Whether this is a bug or a feature depends on
> >your point of view --- but there are folks out there who find it to be
> >a life-saver.  You can find some explanations at
> >http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html
> >
> >  
> >
> >>Is it pretty much universally accepted that I should drop all my
> >>foreign keys?
> >>    
> >>
> >
> >No.  They don't have any effect on SELECT performance in Postgres.
> >They will impact update speed, but that's not your complaint (at the
> >moment).  Don't throw away data integrity protection until you know
> >you need to.
> >
> >			regards, tom lane
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >  
> >
> 
> -- 
> 
> 
> Charles H. Woloszynski
> 
> ClearMetrix, Inc.
> 115 Research Drive
> Bethlehem, PA 18015
> 
> tel: 610-419-2210 x400
> fax: 240-371-3256
> web: www.clearmetrix.com
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.3.1 New install, large queries are slow

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-01-16T18:43:02Z

On Wed, 15 Jan 2003, Roman Fail wrote:

I just had new thoughts.

If you make an index on batchdetail(batchid)
does that help?

I realized that it was doing a merge join
to join d and the (t,b,m) combination when it
was expecting 3 rows out of the latter, and
batchid is presumably fairly selective on
the batchdetail table, right? I'd have expected
a nested loop over the id column, but it
doesn't appear you have an index on it in
batchdetail.

Then I realized that batchheader.batchid and
batchdetail.batchid don't even have the same
type, and that's probably something else you'd
need to fix.

> batchheader has 2.6 million records:
> CREATE TABLE public.batchheader (
>   batchid int8 DEFAULT nextval('"batchheader_batchid_key"'::text) NOT NULL,

> And here's batchdetail too, just for kicks.  23 million records.
> CREATE TABLE public.batchdetail (
>   batchid int4,

Re: 7.3.1 New install, large queries are slow

Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-17T13:29:25Z

I'd love to see this as a TODO item, but I am hardly one to add to the 
list...

Charlie


Bruce Momjian wrote:

>Is this a TODO item?
>
>---------------------------------------------------------------------------
>
>Charles H. Woloszynski wrote:
>  
>
>>I was surprised to hear that JOIN syntax constrained the planner.  We 
>>have a policy of using JOIN syntax to describe the table relationships 
>>and where clauses to describe the selection process for our queries.  It 
>>was our understanding that the JOIN syntax was introduced to support 
>>this approach, but not to contrain the planner.  
>>
>>Is there any way to sell the planner to consider JOIN syntax as 
>>equivalent to WHERE clauses and to not use them to force the planner 
>>down a specific path?  Can we get that added as an option (and then made 
>>available to use JDBC folks as a URL parameter).  It would make my team 
>>very happy :-).
>>
>>
>>I think that making this an option will help all those migrating to 
>>Postgres who did not expect that JOINs forced the planner down specific 
>>plans.    Is  it possible/reasonable to add?
>>
>>Charlie
>>
>>
>>Tom Lane wrote:
>>
>>    
>>
>>>"Roman Fail" <rfail@posportal.com> writes:
>>> 
>>>
>>>      
>>>
>>>>Thanks to everyone for the quick replies!  I'm sure that my lack of
>>>>skill with SQL queries is the main problem.  What's strange to me is
>>>>how MSSQL takes my bad queries and makes them look good anyway.  It
>>>>must have a real smart planner.
>>>>   
>>>>
>>>>        
>>>>
>>>I think more likely the issue is that your use of JOIN syntax is forcing
>>>Postgres into a bad plan.  MSSQL probably doesn't assign any semantic
>>>significance to the use of "a JOIN b" syntax as opposed to "FROM a, b"
>>>syntax.  Postgres does.  Whether this is a bug or a feature depends on
>>>your point of view --- but there are folks out there who find it to be
>>>a life-saver.  You can find some explanations at
>>>http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html
>>>
>>> 
>>>
>>>      
>>>
>>>>Is it pretty much universally accepted that I should drop all my
>>>>foreign keys?
>>>>   
>>>>
>>>>        
>>>>
>>>No.  They don't have any effect on SELECT performance in Postgres.
>>>They will impact update speed, but that's not your complaint (at the
>>>moment).  Don't throw away data integrity protection until you know
>>>you need to.
>>>
>>>			regards, tom lane
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>> 
>>>
>>>      
>>>
>>-- 
>>
>>
>>Charles H. Woloszynski
>>
>>ClearMetrix, Inc.
>>115 Research Drive
>>Bethlehem, PA 18015
>>
>>tel: 610-419-2210 x400
>>fax: 240-371-3256
>>web: www.clearmetrix.com
>>
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>    
>>
>
>  
>

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com