From: Richard Neill <rn214@richardneill.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Date: Sat, 22 Dec 2012 17:46:30 +0000
Message-ID: <50D5F1F6.7090704@richardneill.org> (raw)
In-Reply-To: <CAMkU=1zTU9RtyEAsAAfC5i0r6c-c=i-tVOPfF8J=Q-FgDQ31Qw@mail.gmail.com>
References: <50D22DE2.4090301@richardneill.org>
<28778.1355972790@sss.pgh.pa.us>
<50D3CAC4.3070800@richardneill.org>
<50D3D484.6080100@richardneill.org>
<CAMkU=1zTU9RtyEAsAAfC5i0r6c-c=i-tVOPfF8J=Q-FgDQ31Qw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 21/12/12 05:15, Jeff Janes wrote:
>
>
> - What I'm trying to do is trace the history of the books
> through the system and assign each one a proper unique id.
> So, if I see a book with "parcel_id_code = 37",
> is it a new book (after pid wrap), or is it the same book I saw 1
> minute ago, that hasn't exited the sorter?
>
> I'm not sure how you are implementing this goal, but I don't think it is
> best done by looping over all books (presumably from some other table?)
> and issuing an individual query for each one, if that is what you are
> doing. Some kind of bulk join would probably be more efficient.
It would be nice to do a bulk join, but it's not possible: the query is
time sensitive. Consider:
id/pkey pid timestamp exit_state destination
1 77 -24 hours 1 212
2 77 -18 hours 1 213
3 77 -12 hours 1 45
4 77 -6 hours 1 443
5 77 0 hours null
[in future...]
5 77 0 hours 1 92
6 77 4 hours null
At time +5 minutes, I receive a report that a book with parcel_id 77 has
successfully been delivered to destination 92. So, what I have to do is:
* First, find the id of the most recent book which had pid=77 and where
the exit state is null. (hopefully, but not always, this yields exactly
one row, which in this case is id=5)
* Then update the table to set the destination to 92, where the id=5.
It's a rather cursed query, because:
- the sorter machine doesn't give me full info in each message, only
deltas, and I have to reconstruct the global state.
- pids are reused within hours, but don't increase monotonically,
(more like drawing repeatedly from a shuffled deck, where cards
are only returned to the deck sporadically.
- some pids get double-reported
- 1% of books fall off the machine, or get stuck on it.
- occasionally, messages are lost.
- the sorter state isn't self-consistent (it can be restarted)
The tracker table is my attempt to consistently combine all the state we
know, and merge in the deltas as we receive messages from the sorter
machine. It ends up reflecting reality about 99% of the time.
Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: rn214@richardneill.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <50D5F1F6.7090704@richardneill.org>
* 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