Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

On Fri, May 9, 2008 at 1:18 AM, Antoine Baudoux <[EMAIL PROTECTED]> wrote: > Ok, I've tried everything, and the planner keeps choosing index scans when > it shouldnt. > > Is there a way to disable index scans? You can use "set enable_indexscan off;" as the first command I've had one or two repor

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Ok, I've tried everything, and the planner keeps choosing index scans when it shouldnt. Is there a way to disable index scans? Antoine -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

On Tue, 6 May 2008, Tom Lane wrote: If a misestimate of this kind is bugging you enough that you're willing to change the query, I think you can fix it like this: select ... from foo order by x limit n; => select ... from (select ... from foo order by x) ss limit n; The subselec

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

If a misestimate of this kind is bugging you enough that you're willing to change the query, I think you can fix it like this: select ... from foo order by x limit n; => select ... from (select ... from foo order by x) ss limit n; The subselect will be planned without awarene

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

On Tue, 2008-05-06 at 18:24 +0100, Antoine Baudoux wrote: > Isnt the planner fooled by the index on the sorting column? > If I remove the index the query runs OK. In your case, for whatever reason, the stats say doing the index scan on the sorted column will give you the results faster. That is

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

On Tue, 2008-05-06 at 18:59 +0100, Tom Lane wrote: > Whether the scan is forwards or backwards has nothing > to do with it. The planner is using the index ordering > to avoid having to do a full-table scan and sort. Oh, I know that. I just noticed that when this happened to us, more often than

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Antoine Baudoux wrote: Here is the explain analyse for the first query, the other is still running... explain analyse select * from t_Event event inner join t_Service service on event.service_id=service.id inner join t_System system on service.system_id=system.id inner join t_Interface interfa

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Antoine Baudoux wrote: Here is the explain analyse for the first query, the other is still running... explain analyse select * from t_Event event inner join t_Service service on event.service_id=service.id inner join t_System system on service.system_id=system.id inner join t_Interface interfa

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Shaun Thomas <[EMAIL PROTECTED]> writes: > I'm not sure what causes this, but the problem with indexes is that > they're not necessarily in the order you want unless you also cluster > them, so a backwards index scan is almost always the wrong answer. Whether the scan is forwards or backwards has

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Thanks a lot for your answer, there are some points I didnt understand On May 6, 2008, at 6:43 PM, Shaun Thomas wrote: The second query says "Awesome! Only one network... I can just search the index of t_event backwards for this small result set!" Shouldnt It be the opposite? consi

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

On Tue, 2008-05-06 at 16:03 +0100, Antoine Baudoux wrote: > My understanding is that in the first case the sort is > done after all the table joins and filtering, but in the > second case ALL the rows in t_event are scanned and sorted > before the join. You've actually run into a problem that's b

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Here is the explain analyse for the first query, the other is still running... explain analyse select * from t_Event event inner join t_Service service on event.service_id=service.id inner join t_System system on service.system_id=system.id inner join t_Interface interface on system.id=interfa

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Antoine, On Tue, May 6, 2008 at 5:03 PM, Antoine Baudoux <[EMAIL PROTECTED]> wrote: > "Limit (cost=23981.18..23981.18 rows=1 width=977)" > " -> Sort (cost=23981.18..23981.18 rows=1 width=977)" > "Sort Key: this_.c_date" Can you please provide the EXPLAIN ANALYZE output instead of E

[PERFORM] multiple joins + Order by + LIMIT query performance issue

Hello, I have a query that runs for hours when joining 4 tables but takes milliseconds when joining one MORE table to the query. I have One big table, t_event (8 million rows) and 4 small tables (t_network,t_system,t_service, t_interface, all < 1000 rows). This query takes a few millisecond