I feel the need for speed. What am I doing wrong?

Added -general list so that the next followup can remove -hackers and everyone
there will have had notice.

On Mon, 6 Jan 2003, Dann Corbit wrote:

I have a query using two postgres tables.
One is called "CNX_DS_53_SIS_STU_OPT_FEE_TB" and the other is called
"CNX_DS2_53_SIS_STU_OPT_FEE_TB".

I am getting 3 times slower performance than Microsoft Access when
performing a left outer join.

...

Here is the query:
select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" = b."RT_REC_KEY"
and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ;

Creating the following index had no effect on performance!
create unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY",
"cnxarraycolumn", "CRC");

Both tables had 6139062 rows of data.

In this query ... all rows of data match perfectly, so no results are
returned.

I suspect you get no results because it's unlikely b.oid will be null. Are you
sure the query is how it should be since you seem to be expecting no rows to be
returned and yet your reason for that doesn't match the query as shown. Without
the oid test I'd bet you get a result set of 6139062 rows.

Is there a way to reformulate this query so that it will use the index?

Given the above comment I'd say no since the entirety of both tables will be
tested to make the result set.

Alternatively, if the query is right try something along the lines of:

SELECT a.blah, a.foo,
FROM a, b
WHERE a.blah = b.blah AND a.foo = b.foo AND b.oid IS NULL

if that doesn't use a query try pushing the null test into a subselect like:

SELECT a.blah, a.foo,
FROM a, (SELECT * FROM b WHERE oid IS NULL) b
WHERE a.blah = b.blah AND a.foo = b.foo

After that let's hope I haven't embarrassed myself.

--
Nigel J. Andrews

-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Monday, January 06, 2003 4:58 PM
To: Dann Corbit
Cc: pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [HACKERS] I feel the need for speed. What am I
doing wrong?

Added -general list so that the next followup can remove
-hackers and everyone there will have had notice.

On Mon, 6 Jan 2003, Dann Corbit wrote:

I have a query using two postgres tables.
One is called "CNX_DS_53_SIS_STU_OPT_FEE_TB" and the other is called
"CNX_DS2_53_SIS_STU_OPT_FEE_TB".

I am getting 3 times slower performance than Microsoft Access when
performing a left outer join.

...

Here is the query:
select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" =

b."RT_REC_KEY"

and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ;

Creating the following index had no effect on performance! create
unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY",
"cnxarraycolumn", "CRC");

Both tables had 6139062 rows of data.

In this query ... all rows of data match perfectly, so no

results are

returned.

I suspect you get no results because it's unlikely b.oid will
be null. Are you sure the query is how it should be since you
seem to be expecting no rows to be returned and yet your
reason for that doesn't match the query as shown. Without the
oid test I'd bet you get a result set of 6139062 rows.

There are supposed to be no results for this particular query.
The data is unique with only RT_REC_KEY and cnxarraycolumn (I tried
using that as an index also).

The basic gist of it is like this:

I want to know where the keys match (e.g.: RT_REC_KEY + cnxarraycolumn)
but the CRC has changed (which will tell me what data has changed).
This is for a data synchronization system that uses PostgreSQL as a join
engine. I store primary key data together with a 64 bit CRC in data
tables stored in PostgreSQL. In this particular case, there were no
changes but there will be changes at other times.

Is there a way to reformulate this query so that it will use the
index?

Given the above comment I'd say no since the entirety of both
tables will be tested to make the result set.

Alternatively, if the query is right try something along the lines of:

SELECT a.blah, a.foo,
FROM a, b
WHERE a.blah = b.blah AND a.foo = b.foo AND b.oid IS NULL

OID is never null. I don't think that this query is equivalent. This
Oid is just the standard PostgreSQL Oid, and not some arbitrary field.

if that doesn't use a query try pushing the null test into a
subselect like:

SELECT a.blah, a.foo,
FROM a, (SELECT * FROM b WHERE oid IS NULL) b
WHERE a.blah = b.blah AND a.foo = b.foo

OID is never null. I don't think that this query is equivalent.

After that let's hope I haven't embarrassed myself.

Probably, I did not explain myself clearly enough.

"Dann Corbit" <DCorbit@connx.com> writes:

Creating the following index had no effect on performance!
create unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY",
"cnxarraycolumn", "CRC");

What does EXPLAIN ANALYZE have to say about the query? If you set
enable_seqscan = 0, does the explain result change?

regards, tom lane

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" = b."RT_REC_KEY"
and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ;

I suspect you get no results because it's unlikely b.oid will be null.

Try "it's impossible for b.oid to be null --- unless a dummy b row is
being provided by the LEFT JOIN". I interpret the purpose of the query
to be to look for "a" rows that have no matching "b" row.

Using OID for this is kind of cute, I guess, since it is guaranteed
not-null in a real row; he doesn't have to think about whether any of
his regular columns are not-null.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 06, 2003 5:26 PM
To: Nigel J. Andrews
Cc: Dann Corbit; pgsql-hackers@postgresql.org;
pgsql-general@postgresql.org
Subject: Re: [HACKERS] I feel the need for speed. What am I
doing wrong?

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:

select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" =
b."RT_REC_KEY" and a."cnxarraycolumn" = b."cnxarraycolumn") where
b.oid is null ;

I suspect you get no results because it's unlikely b.oid

will be null.

Try "it's impossible for b.oid to be null --- unless a dummy
b row is being provided by the LEFT JOIN". I interpret the
purpose of the query to be to look for "a" rows that have no
matching "b" row.

Using OID for this is kind of cute, I guess, since it is
guaranteed not-null in a real row; he doesn't have to think
about whether any of his regular columns are not-null.

I am very happy to report that PostgreSQL now easily beats MS Access for
speed!
:-)

Here are the MS Access results, where I create on index and then a
second:
Access Outer Join 2 column index on 1st table
02:29.9
2 column index on 2nd table + 2 column index on 1st
table 02:18.3

For PostgreSQL, I originally created a 3 column index (because I have
also 3 column joins in other places) and got this result:
Postgres Outer Join
enable_seqscan = 1
3 column Index 1 12:43.9

Showing the plan showed that the indexes were being ignored.

Yikes! Five times slower! But then I took Tom's incredibly helpful
suggestion to disable the sequential scan:

Postgres Outer Join
enable_seqscan = 0
3 column Index 0 05:17.5

Changed to a 2 column index:
2 column index 0 04:58.3

Added an index to the second table:
2 column index on 2nd table + 2 column index on 1st
table 0 01:53.6

PostgreSQL is now 22% faster than Access (HAPPY DAYS)!

For my application, I happen to know that the data will be approximately
physically clustered, and that the indexes will always be very near
matches for data sets big enough to matter. Benchmarking also showed
that adding the 3rd column to the index was counter productive, even
when the join criteria was a.f1 = b.f1, a.f2=b.f2, a.f3 = b.f3.

"Dann Corbit" <DCorbit@connx.com> writes:

Yikes! Five times slower! But then I took Tom's incredibly helpful
suggestion to disable the sequential scan:

Ideally, you shouldn't have to do that. Now that you have the correct
indexes in place, could you show us the EXPLAIN ANALYZE output for both
cases (enable_seqscan = on and off)?

Also, you might try leaving enable_seqscan = on, and seeing how far you
have to decrease random_page_cost to get the planner to choose
indexscan.

regards, tom lane

There is a construct that most people forget for that kind of query:

select "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
except
select "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;

simple.

JLL

Tom Lane wrote:

-----Original Message-----
From: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
Sent: Tuesday, January 07, 2003 2:43 PM
To: Tom Lane
Cc: Dann Corbit; Nigel J. Andrews;
pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
What am I doing wrong?

There is a construct that most people forget for that kind of query:

select "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
except
select "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;

simple.

I should have mentioned that I am not using the latest version of
PostgreSQL. I am using 7.1.3. Perhaps this stuff has been repaired in
newer versions. Possibly, there is a reason that people forget to use
it (at least on PostgreSQL 7.1.3):

connxdatasync=> SET enable_seqscan = 0;
SET VARIABLE
connxdatasync=>
connxdatasync=> SELECT a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" FROM
connxdatasync-> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> LEFT OUTER JOIN
connxdatasync-> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b
connxdatasync-> ON ( a."RT_REC_KEY" = b."RT_REC_KEY" AND
a."cnxarraycolumn" = b."cnxarraycolumn")
connxdatasync-> WHERE b.oid IS NULL ;
RT_REC_KEY | cnxarraycolumn | CRC
------------+----------------+-----
(0 rows)

1:55.12 to complete

connxdatasync=>
connxdatasync=> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> except
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
RT_REC_KEY | cnxarraycolumn | CRC
------------+----------------+-----
(0 rows)

12:55.25 to complete: More than 6 times slower to complete.

connxdatasync=>
connxdatasync=> EXPLAIN
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> except
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
NOTICE: QUERY PLAN:

SetOp Except (cost=202028537.97..202120623.90 rows=1227812 width=24)
-> Sort (cost=202028537.97..202028537.97 rows=12278124 width=24)
-> Append (cost=100000000.00..200225099.24 rows=12278124
width=24)
-> Subquery Scan *SELECT* 1
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Subquery Scan *SELECT* 2
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b
(cost=100000000.00..100112549.62 rows=6139062 width=24)

EXPLAIN
connxdatasync=>

-----Original Message-----
From: johnnnnnn [mailto:john@phaedrusdeinus.org]
Sent: Tuesday, January 07, 2003 3:33 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
What am I doing wrong?

On Tue, Jan 07, 2003 at 03:10:06PM -0800, Dann Corbit wrote:

NOTICE: QUERY PLAN:

SetOp Except (cost=202028537.97..202120623.90 rows=1227812

width=24)

-> Sort (cost=202028537.97..202028537.97 rows=12278124 width=24)
-> Append (cost=100000000.00..200225099.24 rows=12278124
width=24)
-> Subquery Scan *SELECT* 1
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Subquery Scan *SELECT* 2
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b
(cost=100000000.00..100112549.62 rows=6139062 width=24)

EXPLAIN

Those big round numbers suggest that you haven't run vacuum
analyze on all of your tables. Since PostgreSQL uses a
cost-based optimizer, you do actually have to give it some
idea of what things will cost before it can give you an
appropriate plan.

Reference for your version:
http://www14.us.postgresql.org/users-lounge/docs/7.1/reference

/sql-vacuum.html

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query. No boost at all. Even
with SET enable_seqscan = 0 it still does a table scan.

--On Tuesday, January 07, 2003 15:25:06 -0800 Dann Corbit
<DCorbit@connx.com> wrote:

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query. No boost at all. Even
with SET enable_seqscan = 0 it still does a table scan.

did you do VACUUM ANALYZE?

If not, the stats weren't updated.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Tue, Jan 07, 2003 at 03:10:06PM -0800, Dann Corbit wrote:

NOTICE: QUERY PLAN:

SetOp Except (cost=202028537.97..202120623.90 rows=1227812 width=24)
-> Sort (cost=202028537.97..202028537.97 rows=12278124 width=24)
-> Append (cost=100000000.00..200225099.24 rows=12278124
width=24)
-> Subquery Scan *SELECT* 1
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Subquery Scan *SELECT* 2
(cost=100000000.00..100112549.62 rows=6139062 width=24)
-> Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b
(cost=100000000.00..100112549.62 rows=6139062 width=24)

EXPLAIN

Those big round numbers suggest that you haven't run vacuum analyze on
all of your tables. Since PostgreSQL uses a cost-based optimizer, you
do actually have to give it some idea of what things will cost before
it can give you an appropriate plan.

Reference for your version:
http://www14.us.postgresql.org/users-lounge/docs/7.1/reference/sql-vacuum.html

-johnnnnnnnnn

"Dann Corbit" <DCorbit@connx.com> writes:

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query. No boost at all.

VACUUM or VACUUM ANALYZE? Standalone ANALYZE was not in 7.1 but
VACUUM ANALYZE does what you need to do...

-Doug

Dann,

I run 7.2.1

I tried adding DISTINCT on one of my table and I get similar
performance.

set enable_seqscan = 0;
select distinct "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
except
select distinct "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;

Dann Corbit wrote:

johnnnnnn <john@phaedrusdeinus.org> writes:

On Tue, Jan 07, 2003 at 03:10:06PM -0800, Dann Corbit wrote:

-> Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
(cost=100000000.00..100112549.62 rows=6139062 width=24)

Those big round numbers suggest that you haven't run vacuum analyze on
all of your tables.

No; the 100000000.00 is a tipoff that he's set enable_seqscan off, but
the system is using a seqscan anyway because it cannot find any other
plan.

"SET enable_seqscan = off" does not prevent the planner from generating
seqscan plans, it just adds 100000000.00 to the cost estimate. That
will generally cause the planner to pick another plan --- if it can find
one. In this case it evidently cannot find any indexscan alternative.

regards, tom lane

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query. No boost at all. Even
with SET enable_seqscan = 0 it still does a table scan.

Dann, I can attest to 7.2 having a much better planner and performance
than 7.1 did. Can you upgrade?

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, January 08, 2003 6:30 AM
To: Dann Corbit
Cc: johnnnnnn; pgsql-hackers@postgresql.org
Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
What am I doing wrong?

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query. No boost at all.
Even with SET enable_seqscan = 0 it still does a table scan.

Dann, I can attest to 7.2 having a much better planner and
performance
than 7.1 did. Can you upgrade?

No, not yet. I am using a native Win32 port that we did ourselves.
Once the PostgreSQL team completes the Win32 port, I will abandon this
code base and use that code. This is for a commercial enterprise and
Cygwin is out of the question because of the distribution problems.