Stephen Frost <sfrost@xxxxxxxxxxx> writes: > * Tom Lane (tgl@xxxxxxxxxxxxx) wrote: >> Yeah, it's spending quite a lot of time finding the first matching row >> in each child table. I'm curious why that is though; are the child >> tables not set up with nonoverlapping firstloadtime ranges? > The issue here is that the query is saying "Give me the first 150 > records with this host_id in this week-long range". Oh, I see. So the query range overlaps multiple child tables, even after constraint exclusion eliminates a lot of them. > My point above is that the CHECK constraints ensure an ordering which > could be leveraged to use the latest table first and then stop if enough > tuples are returned (or immediately go to the next table), without ever > considering the other tables. Yeah. My opinion is that trying to reverse-engineer that from the CHECK constraints would cost a lot more than it's worth. What we need, and will hopefully have sooner or later, is an abstract concept of "partitioned table" in which this kind of relationship is known a-priori instead of having to be laboriously re-deduced every time we plan a query. >> If you're stuck with this table arrangement, one thing that would help >> is a two-column index on (host_guid, firstloadtime) on each child table. > Agreed, I mentioned this to the OP previously and it's on his list of > things to try. AFAICS the fact that this example would be fast if we were only paying attention to the newest table is mere luck. If it can take a long time to find the first matching host_guid record in several of the child tables, why might it not take just as long to find said record in the other one? I think you really need the two-column indexes, if keeping this query's runtime to a minimum is critical. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
