From: Shaun Thomas <sthomas@optionshouse.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Setting Statistics on Functional Indexes
Date: Mon, 29 Oct 2012 10:26:57 -0500
Message-ID: <508EA041.6090101@optionshouse.com> (raw)
In-Reply-To: <3040.1351285698@sss.pgh.pa.us>
References: <50881D73.100@optionshouse.com>
<6668.1351105908@sss.pgh.pa.us>
<508841FF.9040303@optionshouse.com>
<5088478C.6000905@optionshouse.com>
<1511.1351280130@sss.pgh.pa.us>
<508AEB1A.2040001@optionshouse.com>
<3040.1351285698@sss.pgh.pa.us>
On 10/26/2012 04:08 PM, Tom Lane wrote:
> So the bottom line is that this is a case where you need a lot of
> resolution in the histogram. I'm not sure there's anything good
> we can do to avoid that.
I kinda hoped it wouldn't be something like that. For the particularly
painful instance, it was easy to replace the index with a better (if
larger) dual index and drop the bad old one. But in some cases, I'm
having to maintain two indexes that make me sad:
CREATE TABLE activity (
activity_id SERIAL NOT NULL PRIMARY KEY,
account_id BIGINT NOT NULL,
action_date TIMESTAMP WITHOUT TIME ZONE
);
CREATE INDEX idx_activity_action_date_account_id
ON activity (action_date, activity_id);
CREATE INDEX idx_activity_account_id_action_date
ON activity (activity_id, action_date);
Because in the first case, we needed the action_date to be first for
analytics that *don't* supply account_id. But in the second case, we
need the account_id first, so we can get the most recent action(s) for
that account without a very expensive backwards index scan on the first
index.
I know that current_date seems like an edge case, but I can't see how
getting the most recent activity for something is an uncommon activity.
Tip tracking is actually the most frequent pattern in the systems I've
seen. Admittedly, those are almost always high TPS trading systems.
At this point, I'm almost willing to start putting in optimization
fences to force it along the right path. Which is gross, because that's
effectively no better than Oracle hints. But I also don't like setting
my statistics to 5000+ on problematic column/index combos to get the
right heuristics, or having semi-duplicate multi-column indexes to
exploit sorting performance.
I mean, I get it. I just wonder if this particular tweak isn't more of a
regression than initially thought.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com, tgl@sss.pgh.pa.us
Subject: Re: Setting Statistics on Functional Indexes
In-Reply-To: <508EA041.6090101@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox