Given an index like this:
CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
and a query like this:
SELECT * FROM t1 WHERE c1 = 123;
I'd like the planner to be smart enough to use an index scan using i1. Yes,
I can change the query to this:
SELECT * FROM t1 WHERE c1 = 123 AND
I have a very large table (about a million rows) which I most frequently
want to select a subset of rows from base on a date field. That date field
is indexed, and when Postgres uses that index, queries are fast. But
sometimes it decides not to use the index, resorting to a sequential scan
instea
On 1/5/04 1:55 AM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> Obviously the planner is making some bad choices here.
>
> A fair conclusion ...
>
>> I know that it is trying to avoid random seeks or other scary things
>> implied by a "
On 1/5/04 11:45 AM, Christopher Browne wrote:
> It sounds to me as though the statistics that are being collected
> aren't "good enough." That tends to be a sign that the quantity of
> statistics (e.g. - bins in the histogram) are insufficient.
>
> This would be resolved by changing the number of
Speaking of special cases (well, I was on the admin list) there are two
kinds that would really benefit from some attention.
1. The query "select max(foo) from bar" where the column foo has an index.
Aren't indexes ordered? If not, an "ordered index" would be useful in this
situation so that this
On 1/5/04 2:52 PM, Rod Taylor wrote:
> max(foo) optimizations requires an extension to the aggregates system.
> It will likely happen within a few releases.
Looking forward to it.
> A work around can be accomplished today through the use of LIMIT and ORDER BY.
Wowzers, I never imagined that that
Here's the top item from the "top" listing on my Postgres machine:
10:20am up 98 days, 18:28, 3 users, load average: 1.14, 1.09, 0.85
103 processes: 101 sleeping, 1 running, 0 zombie, 1 stopped
CPU0 states: 38.0% user, 13.1% system, 0.0% nice, 48.3% idle
CPU1 states: 39.0% user, 11.3% system,
On 1/17/04 12:32 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> Process 20753 was hovering around 60-70% CPU usage for a long time.
>
>> I tried to see what that postmaster was doing, but it appeared to be "idle"
>
>> % ps -wwwx -p
This is a follow-up to an old thread of mine, but I can't find it now
so I'll just re-summarize.
I have a ~1 million row table that I mostly want to query by date
range. The rows are pretty uniformly spread over a 3 year date range.
I have an index on the date column, but it wasn't always use
On 2/22/04 2:05 PM, Josh Berkus wrote:
> On Saturday 21 February 2004 16:18, John Siracusa wrote:
>> Next, thanks to my earlier thread, I clustered the table on the date
>> column and then "SET STATISTICS" on the date column to be 100. That
>> did the trick, and
On 2/22/04 5:06 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> I want to do something that will convince Postgres that using the date
>> index is, by far, the best plan when running my queries, even when the
>> date column correlation stat drops we
On 2/22/04 6:40 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> I think the key is to get the planner to correctly ballpark the number of
>> rows in the date range.
>
> I thought it was. What you showed was
>
> -> Index Scan using my
On 3/3/04 6:53 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> Given an index like this:
>> CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>> and a query like this:
>> SELECT * FROM t1 WHERE c1 = 123;
>> I'd like th
On 3/3/04 6:53 PM, Tom Lane wrote:
John Siracusa <[EMAIL PROTECTED]> writes:
Given an index like this:
CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
and a query like this:
SELECT * FROM t1 WHERE c1 = 123;
I'd like the planner to be smart enough to use an index
14 matches
Mail list logo
