Re: Optimizing queries that use multiple tables and many order by columns

On 2010-08-25, Joshua Berry wrote:

> Hi Group,
>
> I've never really learned how to optimize queries that join
> several tables and have order by clauses that specify columns
> from each table. Is there documentation that could help me
> optimize and have the proper indexes in place? I've read
> through the PG Docs Chapter 11 on Indexes yet still lack the
> needed understanding.
>
> Here's my latest culprit:
>
> select Anl.Priority, Anl.Lab, Anl.Job, JOB.DateIn,
> JOB.CompanyCode, Anl.SampleName
> from analysis anl join job on anl.job = job.job
> order by job.companycode, anl.job, anl.lab
> limit 10;
>

Could you try to remove the limit clause? I have seen several times,
that it may slow down a query. Although I haven't tested it, that an
OFFSET 0 clause can improve the situation, iirc.

From an algebraic point of view, I cannot see obvious inefficiencies.
Others, which now the internals of pg better, might see more.

> Here's the query plan using PG 8.4.4:
> Limit  (cost=21990.24..21990.27 rows=10 width=32)
>   ->  Sort  (cost=21990.24..22437.69 rows=178979 width=32)
>         Sort Key: job.companycode, anl.lab
>         ->  Hash Join  (cost=451.20..18122.57 rows=178979 width=32)
>               Hash Cond: (anl.job = job.job) ->  Seq Scan on analysis
>               anl (cost=0.00..14091.79 rows=178979 width=23) ->  Hash
>               (cost=287.20..287.20 rows=13120 width=17)
>                     ->  Seq Scan on job  (cost=0.00..287.20
> rows=13120 width=17)
>
>
> If I change the above query to only order by one of the
> tables, I get better results:
> select Anl.Priority, Anl.Lab, Anl.Job, JOB.DateIn,
> JOB.CompanyCode, Anl.SampleName
> from analysis anl join job on anl.job = job.job
> order by job.companycode --, anl.job, anl.lab
> limit 10;
> Limit  (cost=0.00..3.65 rows=10 width=32)
>   ->  Nested Loop  (cost=0.00..65269.13 rows=178979 width=32)
>         ->  Index Scan using job_companycode on job (cost=0.00..972.67
>         rows=13120 width=17) ->  Index Scan using analysis_job_lab on
>         analysis anl
>  (cost=0.00..4.63 rows=22 width=23)
>               Index Cond: (anl.job = job.job)
>

That are estimated query plans, what does EXPLAIN ANALYZE say? The query
plans above do not execute the query instead they just make a rough
guess about the costs. Reality might be different. Also you may want to
run VACUUM ANALYZE before.

> Any idea on how I can improve this? In the past I would tend
> to create a cached copy of the query as a table that would be
> utilized, but I suspect that there is a better way to go
> about this. I'm using a system (Clarion) which heavily uses
> cursors via the ODBC driver (I use the psqlODBC latest
> version) to get a handful of records at a time, so no actual
> LIMITs would be used in the production queries; I've added
> the LIMITs here to try to simulate the performance
> differences that I find when browsing the data while ordering
> by the above columns.
>
>
> Here are the relevant tables and indexes:
>
>
> CREATE TABLE job
> (
>   job bigint NOT NULL, -- Job #
>   companycode character(4), -- Company Code
>   recdbycode character(3), -- Initials of who checked in sample(s)
>   datein date, -- Date sample was received
>   project character varying, -- Project or Site name
>   remarks text, -- Remarks
>   --[CONSTRAINTs etc]
> )
>
> CREATE INDEX job_companycode
>   ON job
>   USING btree
>   (companycode);
> CREATE INDEX job_companycode_job
>   ON samples.job
>   USING btree
>   (companycode, job);
>

Index job_companycode is not used in the plans. Additionally, it can be
constructed from the second index, as companycode is the primary sort
key.

> CREATE TABLE analysis
> (
>   lab bigint NOT NULL, -- Lab number
>   job bigint, -- Job number
>   sampletype character varying(5), -- General class of sample
>   priority character(1), -- Priority level
>   samplename character varying, -- Sample or Well name
>   CONSTRAINT rel_joblabk_to_jobk FOREIGN KEY (job)
>       REFERENCES job (job) MATCH SIMPLE
>       ON UPDATE CASCADE ON DELETE RESTRICT,
>   --[CONSTRAINTs etc]
> )
>
> CREATE INDEX analysis_companycode_job_lab
>   ON analysis
>   USING btree
>   (companycode, job, lab);
>
> CREATE INDEX analysis_job_lab
>   ON analysis
>   USING btree
>   (job, lab);
>

Maybe, the planner decides for a Sort Join, if there are sorted indexes
for anl.job and job.job. But the speed-up may vary depending on the
data.

> Thanks for any insights and tips you can provide!
>
> Kind Regards,
> -Joshua Berry
>
>
>

HTH

--
Robert...