pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slow 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>
  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 06:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 07:46  Alex Johnson <alex@aretesystems.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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