From: | Ludwig Lim <lud_nowhere_man(at)yahoo(dot)com> |
---|---|
To: | PostgreSQL Mailing List <pgsql-sql(at)postgresql(dot)org> |
Subject: | Tuning complicated query |
Date: | 2002-09-26 09:57:29 |
Message-ID: | 20020926095729.45949.qmail@web80310.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi:
Attached to the e-mail is the body of the query and
the result of the EXPLAIN (Sorry for not placing the
query and EXPLAIN in the e-mail body . The query is
rather complicated and the EXPLAIN result is rather
long ).
The file demo.out.3 is the result of the EXPLAIN
The file demo.sql is the sql statement.
I would like your opinion on how to tune the query
as posted in the attachment
Note that I have indexes on the all the column
customer_id on both sc_customer_attr and
sc_add_points.
I am wondering why sequential scan was used the on
the clause a.customer_id = b.customer_id since the
previous join condition has an "exist" subquery with
LIMIT with filters out unneccesary customer_id before
performing the join (a_customer_id = b.customer_id).
Also I was wondering why the number of rows in the
last sequential scan is still 7 million plus (most of
the should already have been elimated by the
subquery).
Note that before the executing the query, the
database has been VACUUMed and ANALYZEd. The result of
EXPLAIN ANALYZE is almost similar to one produce by
issuing the EXPLAIN.
Any hints on tuning the query?
thank you
ludwig
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
Attachment | Content-Type | Size |
---|---|---|
demo.out.3 | text/plain | 2.3 KB |
demo.sql3 | text/plain | 1.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | John Sebastian N. Mayordomo | 2002-09-26 10:56:46 | start and end of the week |
Previous Message | Michael Paesold | 2002-09-25 20:21:24 | Re: Getting current transaction id |