On Tue, 5 Aug 2003 15:26:09 -0700, Sean Chittenden <[EMAIL PROTECTED]> wrote: >> I have an experimental patch lying around somewhere that tries to >> work around these problems by offering different estimation methods >> for index scans. If you are interested, I'll dig it out. > >Sure, I'll take a gander... had my head in enough Knuth recently to >even hopefully have some kind of a useful response to the patch.
Sean, the patch is at http://www.pivot.at/pg/16-correlation-732.diff. A short description of its usage can be found at http://archives.postgresql.org/pgsql-performance/2002-11/msg00256.php. If you are interested how the different interpolation methods work, read the source - it shouldn't be too hard to find. You might also want to read the thread starting at http://archives.postgresql.org/pgsql-hackers/2002-10/msg00072.php. >> does this mean that sensorid, evtime, and action are not >> independent? > >Hrm... sensorid is sequence and grows proportional with evtime, >obviously. So a *low* sensorid (7) is quite uncommon for a *late* evtime? This would help understand the problem. Unfortunately I have no clue what to do about it. :-( >Having spent a fair amount of time looking at the two following plans, >it seems as though an additional statistic is needed to change the >cost of doing an index lookup when the index is linearly ordered. I'm not sure I understand what you mean by "index is linearly ordered", but I guess correlation is that statistic you are talking about. However, it is calculated per column, not per index. >Whether CLUSTER does this or not, I don't know, If you CLUSTER on an index and then ANALYSE, you get a correlation of 1.0 (== optimum) for the first column of the index. > I never heard back >from him after getting the runtime down to a few ms. :-/ Pity! I'd have liked to see EXPLAIN ANALYSE for SELECT * FROM mss_fwevent WHERE sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL) AND NOT action; SELECT * FROM mss_fwevent WHERE sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL); SELECT * FROM mss_fwevent WHERE evtime > (now() - '6 hours'::INTERVAL); SELECT * FROM mss_fwevent WHERE sensorid = 7; > Are indexes >on linearly ordered data rebalanced somehow? I thought CLUSTER only >reordered data on disk. -sc AFAIK CLUSTER re-creates all indices belonging to the table. Servus Manfred ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
