Search Postgresql Archives

Re: [ADMIN] OUTER JOIN IS SLOW

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, 23 Dec 2006, Benjamin Arai wrote:

"-> Index Scan using mutualfd_weekday_qbid_pkey_idx on mutualfd_weekday_qbid (cost=0.00..6.01 rows=1 width=19) (actual time=34.579..8510.801 rows=253 loops=1)"

You're right that this is the problem and show that the planner was expecting a very low cost on the index scan, but it turned out to be much higher. This is because of old statistics. VACUUM ANALYZE should remedy this.

Just run:

ANALYZE mytablename;

in psql and see if that gets you going.

Also of note, you can set work_mem per session, so it's reasonable to benchmark various values until you find one that helps your query.

Note that you might not want to set work_mem too high, because it's per operation, so you could easily run your machine out of RAM if you set this too high and have many concurrent queries running.

Also, to answer your question regarding FSM settings, you should run a "vacuumdb -av" and look at the last 8 lines to see if you have your FSM settings high enough.


is the problem. As I understand it is using the index but it is low as dirt. Hopefully, the -z will fix this. I also ran the same query but with earlier dates in the table and the query ran much faster.

Merge Full Join (cost=3492.48..3505.60 rows=1034 width=19) (actual time=7.605..12.851 rows=273 loops=1)
 Merge Cond: ("outer".d1 = "inner".pkey)
-> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=4.586..5.530 rows=263 loops=1)
       Sort Key: d1.d1
-> Function Scan on "getDateRange" d1 (cost=0.00..12.50 rows=1000 width=4) (actual time=2.493..3.515 rows=263 loops=1) -> Sort (cost=3430.15..3432.74 rows=1034 width=19) (actual time=2.998..3.971 rows=263 loops=1)
       Sort Key: mutualfd_weekday_qbid.pkey
-> Index Scan using mutualfd_weekday_qbid_pkey on mutualfd_weekday_qbid (cost=0.00..3378.38 rows=1034 width=19) (actual time=0.075..1.843 rows=263 loops=1) Index Cond: ((cusip = '92193920'::text) AND (pkey >= '1999-12-15'::date) AND (pkey <= '2000-12-15'::date))
Total runtime: 13.935 ms
(10 rows)

In this case it only took 13.935ms as compared to 8522.894 ms for the newer data.

Benjamin

Shoaib Mir wrote:
....adding to the last email, for now try the work_mem but you should be adding ANALYZE along with the VACUUM (with a cron job I guess) you do regularly.

------------
Shoaib Mir
EntperpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com>)

On 12/24/06, *Shoaib Mir* <shoaibmir@xxxxxxxxx <mailto:shoaibmir@xxxxxxxxx>> wrote:

    Try increasing the work_mem first to see the change, that might help.

    -------------
    Shoaib Mir
    EnterpriseDB ( www.enterprisedb.com <http://www.enterprisedb.com>)

    On 12/24/06, *Benjamin Arai* <benjamin@xxxxxxxxxxxx
    <mailto:benjamin@xxxxxxxxxxxx>> wrote:

        I have been running pieces of my PL function by hand and I
        have found
        that the following queries work by themselves taking less than
        a second
        to execute.

        getDateRange"('12/1/2005','12/1/2006')  <- simply generates a
        date
        list.  Doesn't even access a table

        SELECT * FROM mutualfd_weekday_qbid WHERE cusip='92193920'
        AND  pkey >=
        '12/15/2005' AND pkey <= '12/15/2006';

        But when combined as below it takes 10 seconds to execute.

        SELECT d1 as date, d2.data as data FROM
        "getDateRange"('12/1/2005','12/1/2006') d1 FULL OUTER JOIN
        (SELECT *
        FROM mutualfd_weekday_qbid WHERE cusip='92193920' AND  pkey >=
        '12/15/2005' AND pkey <= '12/15/2006') d2 ON d1=d2.pkey;

        Do I need to increase the work_mem or is this possible still a
        ANALYZE
        issue?

        Benjamin

        ---------------------------(end of
        broadcast)---------------------------
        TIP 7: You can help support the PostgreSQL project by donating at

                        http://www.postgresql.org/about/donate
        <http://www.postgresql.org/about/donate>




---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

              http://www.postgresql.org/about/donate



--
Jeff Frost, Owner 	<jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux