From: | secret <secret(at)kearneydev(dot)com> |
---|---|
To: | PG-SQL <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] How to optimize a query... |
Date: | 1999-03-10 15:38:04 |
Message-ID: | 36E691DC.3EA90B99@kearneydev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stuart Rison | 1999-03-10 17:01:15 | DISTINCT within aggregates. |
Previous Message | Tom Lane | 1999-03-10 15:37:20 | Re: [SQL] Performance |