pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: secret <secret@kearneydev.com>
To: PG-SQL <pgsql-sql@postgresql.org>
Subject: How to optimize a query...
Date: Tue, 09 Mar 1999 13:32:16 -0500
Message-ID: <36E56930.7F57299D@kearneydev.com> (raw)

    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




view thread (4+ messages)  latest in thread

Message-ID: <36E56930.7F57299D@kearneydev.com>
Permalink:  ../36E56930.7F57299D@kearneydev.com/
Also on:    postgresql.org/message-id/36E56930.7F57299D@kearneydev.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: secret@kearneydev.com
  Subject: Re: How to optimize a query...
  In-Reply-To: <36E56930.7F57299D@kearneydev.com>
  References: <36E56930.7F57299D@kearneydev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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