pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow performance with join on many fields
3+ messages / 2 participants
[nested] [flat]
* Slow performance with join on many fields
@ 2003-03-04 05:48 Alex Johnson <alex@aretesystems.com>
2003-03-04 06:27 ` Re: Slow performance with join on many fields Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Alex Johnson @ 2003-03-04 05:48 UTC (permalink / raw)
To: pgsql-performance
Hello-
I'm working with a MS Access database that a client wants to turn into
a multi-user database. I'm evaluating PostgreSQL for that purpose (I'd
_really_ like to be able to recommend and open-source solution to
them). However, I'm running into a performance-related issue that I was
hoping this list could help me with.
I have three tables: tbl_samples (~2000 rows), tbl_tests (~4000 rows),
and tbl_results (~20,000 rows), with one-to-many relationships between
them (cascading, in the order given - table definitions are attached
below). I'm looking at the following query that joins these three
tables:
SELECT
tbl_samples.station_id,
tbl_samples.samp_date,
tbl_samples.matrix,
tbl_samples.samp_type_code,
tbl_samples.samp_no,
tbl_samples.field_samp_id,
tbl_tests.method,
tbl_tests.lab,
tbl_results.par_code,
tbl_results.val_qualifier,
tbl_results.value,
tbl_results.units,
tbl_results.mdl,
tbl_results.date_anal
FROM
(tbl_samples
INNER JOIN tbl_tests USING
(station_id,
samp_date,
matrix,
samp_type_code,
samp_no,
samp_bdepth,
samp_edepth)
)
INNER JOIN tbl_results USING
(station_id,
samp_date,
matrix,
samp_type_code,
samp_no,
samp_bdepth,
samp_edepth,
method);
In Access, this query runs in about a second. In PostgreSQL on the same
machine, it takes about 12-15 seconds for the initial rows to be
returned, and about 45 seconds to returns all rows. (This is consistent
whether I use psql, use the pgAdminII SQL window, or use Access with
the ODBC driver.)
This is the output from EXPLAIN:
Nested Loop (cost=437.73..1216.02 rows=1 width=245)
Join Filter: ("outer".method = "inner".method)
-> Merge Join (cost=437.73..461.38 rows=125 width=131)
Merge Cond: (("outer".matrix = "inner".matrix) AND
("outer".samp_edepth = "inner".samp_edepth) AND ("outer".samp_bdepth =
"inner".samp_bdepth) AND ("outer".samp_no = "inner".samp_no) AND
("outer".samp_type_code = "inner".samp_type_code) AND
("outer".samp_date = "inner".samp_date) AND ("outer".station_id =
"inner".station_id))
-> Sort (cost=117.51..120.77 rows=1304 width=63)
Sort Key: tbl_samples.matrix, tbl_samples.samp_edepth,
tbl_samples.samp_bdepth, tbl_samples.samp_no,
tbl_samples.samp_type_code, tbl_samples.samp_date,
tbl_samples.station_id
-> Seq Scan on tbl_samples (cost=0.00..50.04 rows=1304
width=63)
-> Sort (cost=320.22..328.68 rows=3384 width=68)
Sort Key: tbl_tests.matrix, tbl_tests.samp_edepth,
tbl_tests.samp_bdepth, tbl_tests.samp_no, tbl_tests.samp_type_code,
tbl_tests.samp_date, tbl_tests.station_id
-> Seq Scan on tbl_tests (cost=0.00..121.84 rows=3384
width=68)
-> Index Scan using tbl_results_pkey on tbl_results
(cost=0.00..5.99 rows=1 width=114)
Index Cond: (("outer".station_id = tbl_results.station_id) AND
("outer".samp_date = tbl_results.samp_date) AND ("outer".matrix =
tbl_results.matrix) AND ("outer".samp_type_code =
tbl_results.samp_type_code) AND ("outer".samp_no = tbl_results.samp_no)
AND ("outer".samp_bdepth = tbl_results.samp_bdepth) AND
("outer".samp_edepth = tbl_results.samp_edepth))
I've done the following to try to improve performance:
-postgresql.conf:
increased shared_buffers to 384
increased sort_mem to 2048
-clustered all tables on the pkey index
-made sure the joined fields are indexed (they are through the pkeys)
As a note, vm_stat shows no paging while the query is run. Also, I
realize that these keys are large (as in the number of fields). I'll be
condensing these down to sequential IDs (e.g. a SERIAL type) for a
further test, but I'm curious why Access seems to outperform Postgres
in this instance.
My question is, am I missing anything? PostgreSQL will be a hard sell
if they have to take a performance hit.
Thanks for any suggestions you can provide. Sorry for the long e-mail,
but I wanted to provide enough info to diagnose the issue.
Alex Johnson
________________________________
Table defs:
CREATE TABLE tbl_Samples (
Station_ID VARCHAR (25) NOT NULL,
Samp_Date TIMESTAMP WITH TIME ZONE NOT NULL,
Matrix VARCHAR (10) NOT NULL,
Samp_Type_Code VARCHAR (5) NOT NULL,
Samp_No INTEGER NOT NULL,
Samp_BDepth DOUBLE PRECISION NOT NULL,
Samp_EDepth DOUBLE PRECISION NOT NULL,
Depth_units VARCHAR (3),
Samp_start_time TIME,
Samp_end_time TIME,
Field_Samp_ID VARCHAR (20),
Lab_Samp_ID VARCHAR (20),
Samp_Meth VARCHAR (20),
...snip...
PRIMARY KEY
(Station_ID,Samp_Date,Matrix,Samp_Type_Code,Samp_No,Samp_BDepth,Samp_EDe
pth)
);
CREATE TABLE tbl_Tests (
Station_ID VARCHAR (25) NOT NULL,
Samp_Date TIMESTAMP WITH TIME ZONE NOT NULL,
Matrix VARCHAR (10) NOT NULL,
Samp_Type_Code VARCHAR (5) NOT NULL,
Samp_No INTEGER NOT NULL,
Samp_BDepth DOUBLE PRECISION NOT NULL,
Samp_EDepth DOUBLE PRECISION NOT NULL,
Method VARCHAR (50) NOT NULL,
Lab VARCHAR (10) NOT NULL,
Date_Rec TIMESTAMP WITH TIME ZONE,
...snip...
PRIMARY KEY
(Station_ID,Samp_Date,Matrix,Samp_Type_Code,Samp_No,Samp_BDepth,Samp_EDe
pth,Method)
);
CREATE TABLE tbl_Results (
Station_ID VARCHAR (25) NOT NULL,
Samp_Date TIMESTAMP WITH TIME ZONE NOT NULL,
Matrix VARCHAR (10) NOT NULL,
Samp_Type_Code VARCHAR (5) NOT NULL,
Samp_No INTEGER NOT NULL,
Samp_BDepth DOUBLE PRECISION NOT NULL,
Samp_EDepth DOUBLE PRECISION NOT NULL,
Method VARCHAR (50) NOT NULL,
Par_code VARCHAR (50) NOT NULL,
Val_Qualifier VARCHAR (50) NOT NULL,
Value DECIMAL (20,9) NOT NULL,
...snip...
PRIMARY KEY
(Station_ID,Samp_Date,Matrix,Samp_Type_Code,Samp_No,Samp_BDepth,Samp_EDe
pth,Method,Par_code)
);
ALTER TABLE tbl_Tests ADD CONSTRAINT REL_1 FOREIGN KEY
(Station_ID,Samp_Date,Matrix,Samp_Type_Code,Samp_No,Samp_BDepth,Samp_EDe
pth)
REFERENCES tbl_Samples ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE tbl_Results ADD CONSTRAINT REL_2 FOREIGN KEY
(Station_ID,Samp_Date,Matrix,Samp_Type_Code,Samp_No,Samp_BDepth,Samp_EDe
pth,Method)
REFERENCES tbl_Tests ON DELETE CASCADE ON UPDATE CASCADE;
________________________________________________________________________
______
A r e t e S y s t e m s
Alexander M. Johnson, P.E.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow performance with join on many fields
2003-03-04 05:48 Slow performance with join on many fields Alex Johnson <alex@aretesystems.com>
@ 2003-03-04 06:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-04 07:46 ` Re: Slow performance with join on many fields Alex Johnson <alex@aretesystems.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2003-03-04 06:27 UTC (permalink / raw)
To: Alex Johnson <alex@aretesystems.com>; +Cc: pgsql-performance
Alex Johnson <alex@aretesystems.com> writes:
> I'm looking at the following query that joins these three
> tables:
> SELECT ...
> FROM
> (tbl_samples
> INNER JOIN tbl_tests USING ...
> )
> INNER JOIN tbl_results USING ...
You're forcing the join order; perhaps another order is preferable? See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html
> This is the output from EXPLAIN:
EXPLAIN ANALYZE output would've been more useful (it would have shown
whether a different join order would be better, for one thing).
> I've done the following to try to improve performance:
> increased shared_buffers to 384
That's on the picayune side yet. 1000 buffers or so is where you want
to be, I think. Also, have you run ANALYZE or VACUUM ANALYZE lately?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow performance with join on many fields
2003-03-04 05:48 Slow performance with join on many fields Alex Johnson <alex@aretesystems.com>
2003-03-04 06:27 ` Re: Slow performance with join on many fields Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-04 07:46 ` Alex Johnson <alex@aretesystems.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alex Johnson @ 2003-03-04 07:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom-
Thanks for the speedy reply.
> That's on the picayune side yet. 1000 buffers or so is where you want
> to be, I think. Also, have you run ANALYZE or VACUUM ANALYZE lately?
VACUUM ANALYSE did it.... (doh!...now I feel stupid). I had run VACUUM
and VACUUM ANALYZE from pgAdmin, yesterday. After running it from the
command line now, It's much improved (~ 2-3 secs). I'm now looking
into getting my kernel to increase the SHMAX parameter so I can bump up
the shared buffers some more.
Thanks again for the speedy help, and sorry for the obvious goof.
Alex Johnson
________________________________________________________________________
______
A r e t e S y s t e m s
Alexander Johnson, P.E.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-04 07:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-04 05:48 Slow performance with join on many fields Alex Johnson <alex@aretesystems.com>
2003-03-04 06:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-04 07:46 ` Alex Johnson <alex@aretesystems.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox