pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to optimize a query...
4+ messages / 3 participants
[nested] [flat]

* How to optimize a query...
@ 1999-03-09 18:32 secret <secret@kearneydev.com>
  1999-03-09 18:56 ` Re: [SQL] How to optimize a query... Ross J. Reedstrom <reedstrm@rice.edu>
  1999-03-10 15:38 ` Re: [SQL] How to optimize a query... secret <secret@kearneydev.com>
  0 siblings, 2 replies; 4+ messages in thread

From: secret @ 1999-03-09 18:32 UTC (permalink / raw)
  To: pgsql-sql

    I originally had this query with an OR clause instead of the UNION,
which made it amazingly impossibly slow, with the UNION it's far faster
however it's still very slow,(10,000+ rows in both stables), is there a
way to speed this up?  I have BTREE indexes on all concerned fields of
the tables, but it doesn't seem to use any of them...

    I have another query that joins the result of this with 5 other
tables, unfortunately that one takes like 10 minutes...  Any suggestions
on optimizations would be very appreciated.

ftc=> explain
ftc-> select po_id,ticket_pk
ftc-> from tickets,po
ftc-> where po_id=material_po
ftc-> union
ftc-> select po_id,ticket_pk
ftc-> from tickets,po
ftc-> where po_id=trucking_po
ftc-> ;
NOTICE:  QUERY PLAN:

Unique  (cost=4744.05 size=0 width=0)
  ->  Sort  (cost=4744.05 size=0 width=0)
        ->  Append  (cost=4744.05 size=0 width=0)
                ->  Hash Join  (cost=2372.03 size=11659303 width=12)
                      ->  Seq Scan on tickets  (cost=849.03 size=19213
width=8)
                      ->  Hash  (cost=0.00 size=0 width=0)
                            ->  Seq Scan on po  (cost=528.98 size=10848
width=4)
                ->  Hash Join  (cost=2372.03 size=13838477 width=12)
                      ->  Seq Scan on tickets  (cost=849.03 size=19213
width=8)
                      ->  Hash  (cost=0.00 size=0 width=0)
                            ->  Seq Scan on po  (cost=528.98 size=10848
width=4)

EXPLAIN




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] How to optimize a query...
  1999-03-09 18:32 How to optimize a query... secret <secret@kearneydev.com>
@ 1999-03-09 18:56 ` Ross J. Reedstrom <reedstrm@rice.edu>
  1 sibling, 0 replies; 4+ messages in thread

From: Ross J. Reedstrom @ 1999-03-09 18:56 UTC (permalink / raw)
  To: pgsql-sql

secret wrote:
<description of sloq query, with proper EXPLAIN output... thanks!>

Hmm, it looks like your tables are big enough to benefit from indices,
but the query plan didn't choose any. I'd guess an index on po.po_id,
and perhaps tickets.material_po and tickets.trucking_po might help.  

From what I understand, indices are of most use on attributes (fields)
that are mostly unique, like your po_id is (probably). They can hurt you
if you build them on fields that are mostly _not_ unique (the classic
example being a gender field - only two likely values, lost of rows
returned for either, so you scan anyway...)

Any real experts out there to correct me? ;-)

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] How to optimize a query...
  1999-03-09 18:32 How to optimize a query... secret <secret@kearneydev.com>
@ 1999-03-10 15:38 ` secret <secret@kearneydev.com>
  1 sibling, 0 replies; 4+ messages in thread

From: secret @ 1999-03-10 15:38 UTC (permalink / raw)
  To: pgsql-sql

secret wrote:

>     I originally had this query with an OR clause instead of the UNION,
> which made it amazingly impossibly slow, with the UNION it's far faster
> however it's still very slow,(10,000+ rows in both stables), is there a
> way to speed this up?  I have BTREE indexes on all concerned fields of
> the tables, but it doesn't seem to use any of them...
>
>     I have another query that joins the result of this with 5 other
> tables, unfortunately that one takes like 10 minutes...  Any suggestions
> on optimizations would be very appreciated.
>
> ftc=> explain
> ftc-> select po_id,ticket_pk
> ftc-> from tickets,po
> ftc-> where po_id=material_po
> ftc-> union
> ftc-> select po_id,ticket_pk
> ftc-> from tickets,po
> ftc-> where po_id=trucking_po
> ftc-> ;
> NOTICE:  QUERY PLAN:
>
> Unique  (cost=4744.05 size=0 width=0)
>   ->  Sort  (cost=4744.05 size=0 width=0)
>         ->  Append  (cost=4744.05 size=0 width=0)
>                 ->  Hash Join  (cost=2372.03 size=11659303 width=12)
>                       ->  Seq Scan on tickets  (cost=849.03 size=19213
> width=8)
>                       ->  Hash  (cost=0.00 size=0 width=0)
>                             ->  Seq Scan on po  (cost=528.98 size=10848
> width=4)
>                 ->  Hash Join  (cost=2372.03 size=13838477 width=12)
>                       ->  Seq Scan on tickets  (cost=849.03 size=19213
> width=8)
>                       ->  Hash  (cost=0.00 size=0 width=0)
>                             ->  Seq Scan on po  (cost=528.98 size=10848
> width=4)
>
> EXPLAIN

    I'm having a lot of problems with performance under PostgreSQL, it seems
most of my major queries arn't using indexes(as above)... tickets has 15k
tables, 10k tables... The above query takes about 4 minutes...

    Unfortunately I need to add in joins to about 5 tables.... If I add one
more table it's 10 minutes... 2?  Who knows, I know by 5 it's unacceptable,
can anyone please give me some advise on what my problems might be with
queries?  I've done VACUUM ANALYZE ... I didn't used to do that, could it be
missing data from way back?  Should I reload the entire database?

--David




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to optimize a query...
@ 1999-03-10 15:27 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 1999-03-10 15:27 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: pgsql-sql@hub.org

>     I originally had this query with an OR clause instead of the UNION,
> which made it amazingly impossibly slow, with the UNION it's far faster
> however it's still very slow,(10,000+ rows in both stables), is there a
> way to speed this up?

Hmm.  What does EXPLAIN give as the query plan if you use the OR
approach?  The UNION method requires much more work than a decent OR
plan would need; as you can see from the query plan, it has to sort and
unique-ify the results of the two sub-selects in order to discard
duplicate tuples.  If you don't mind seeing the same tuple twice when
it matches on both PO fields, you could use UNION ALL rather than UNION
to avoid the sort step.  But that's just a hack...

I think the problem might be that Postgres doesn't know how to handle
ORed join clauses very efficiently.  Something to work on in the future.

>     I have another query that joins the result of this with 5 other
> tables, unfortunately that one takes like 10 minutes...

How long does it take to EXPLAIN that query?  Postgres' optimizer has
some bugs that cause it to take an unreasonable amount of time to plan
a query that joins more than a few tables.  You can tell whether it's
the planning or execution time that's the problem by comparing EXPLAIN
runtime to the actual query.  (The bugs are fixed in 6.5, btw.  In the
meantime a workaround is to reduce the GEQO threshold to less than the
number of tables in the query that's giving you trouble.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-03-10 15:38 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-09 18:32 How to optimize a query... secret <secret@kearneydev.com>
1999-03-09 18:56 ` Ross J. Reedstrom <reedstrm@rice.edu>
1999-03-10 15:38 ` secret <secret@kearneydev.com>
1999-03-10 15:27 Re: How to optimize a query... Tom Lane <tgl@sss.pgh.pa.us>

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