Re: Index Scan taking long time

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

 



The nested loops (which are due to the joins) don't seem to be part of the problem at all. The main time that is taken (actual time that is) is in this part:
   Index Scan using event_20090526_domain_idx on event_20090526 e
(cost=0.00..10694.13 rows=3606 width=1276) (actual time=50.233..14305.211 rows=3453 loops=1)
       Index Cond: (e.domain_id = d.id)

Which is the leaf node in the query plan, the total time for the query being: Total runtime: 14380.000 ms

And as I said once that query is run once it then does the same query plan and has this output for the same leaf node above:
   Index Scan using event_20090526_domain_idx on event_20090526 e
(cost=0.00..10694.13 rows=3606 width=1276) (actual time=0.027..7.510 rows=3453 loops=1)
       Index Cond: (e.domain_id = d.id)

So it seems to me that once the index is in memory everything is fine with the world, but the loading of the index into memory is horrendous.


Tom Lane wrote:
Scott Marlowe <scott.marlowe@xxxxxxxxx> writes:
Without looking at the explain just yet, it seems to me that you are
constraining the order of joins to insist that the left joins be done
first, then the regular joins second, because of your mix of explicit
and implicit join syntax.  The query planner is constrained to run
explicit joins first, then implicit if I remember correctly.

That isn't true as of recent releases (8.2 and up, I think).  It is true
that there are semantic constraints that prevent certain combinations
of inner and outer joins from being rearranged ... but if that applies
here, it would also prevent manual rearrangement, unless the OP decides
that this query doesn't express quite what he meant.

			regards, tom lane

--

*Bryce Ewing *| Platform Architect
*DDI:* +64 9 950 2195 *Fax:* +64 9 302 0518
*Mobile:* +64 21 432 293 *Freephone:* 0800 SMX SMX (769 769)
Level 11, 290 Queen Street, Auckland, New Zealand | SMX Ltd | smx.co.nz <http://smx.co.nz>
SMX | Business Email Specialists
The information contained in this email and any attachments is confidential. If you are not the intended recipient then you must not use, disseminate, distribute or copy any information contained in this email or any attachments. If you have received this email in error or you are not the originally intended recipient please contact SMX immediately and destroy this email.


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux