pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner statistics, correlations
8+ messages / 4 participants
[nested] [flat]
* Planner statistics, correlations
@ 2007-01-12 08:16 Tobias Brox <tobias@nordicbet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tobias Brox @ 2007-01-12 08:16 UTC (permalink / raw)
To: pgsql-performance
We have a table with a timestamp attribute (event_time) and a state flag
which usually changes value around the event_time (it goes to 4). Now
we have more than two years of events in the database, and around 5k of
future events.
It is important to frequently pick out "overdue events", say:
select * from events where state<>4 and event_time<now()
This query would usually yield between 0 and 100 rows - however, the
planner doesn't see the correlation betewen state and event_time - since
most of the events have event_time<now, the planner also assumes most of
the events with state<>4 has event_time<now, so the expected number of
rows is closer to 5k. This matters, because I have a query with joins,
and I would really benefit from nested loops.
(I've tried replacing "now()" above with different timestamps from the
future and the past. I'm using pg 8.2)
Any suggestions?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 08:56 Peter Childs <peterachilds@gmail.com>
parent: Tobias Brox <tobias@nordicbet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Childs @ 2007-01-12 08:56 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On 12/01/07, Tobias Brox <tobias@nordicbet.com> wrote:
> We have a table with a timestamp attribute (event_time) and a state flag
> which usually changes value around the event_time (it goes to 4). Now
> we have more than two years of events in the database, and around 5k of
> future events.
>
> It is important to frequently pick out "overdue events", say:
>
> select * from events where state<>4 and event_time<now()
>
> This query would usually yield between 0 and 100 rows - however, the
> planner doesn't see the correlation betewen state and event_time - since
> most of the events have event_time<now, the planner also assumes most of
> the events with state<>4 has event_time<now, so the expected number of
> rows is closer to 5k. This matters, because I have a query with joins,
> and I would really benefit from nested loops.
>
> (I've tried replacing "now()" above with different timestamps from the
> future and the past. I'm using pg 8.2)
>
> Any suggestions?
>
Can you say what state might be rather than what it is not. I'm guess
that state is an int but there is only a limited list of possible
states, if you can say what it might be rather than what it is the
index is more liklly to be used.
Peter.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 09:05 Tobias Brox <tobias@nordicbet.com>
parent: Peter Childs <peterachilds@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tobias Brox @ 2007-01-12 09:05 UTC (permalink / raw)
To: Peter Childs <peterachilds@gmail.com>; +Cc: pgsql-performance
[Peter Childs - Fri at 08:56:54AM +0000]
> Can you say what state might be rather than what it is not. I'm guess
> that state is an int but there is only a limited list of possible
> states, if you can say what it might be rather than what it is the
> index is more liklly to be used.
explain select * from events where state in (1,2,3) and event_time<now()
also estimates almost 5k of rows. I also tried:
explain select * from events where state=2 and event_time<now()
but get the same behaviour.
Maybe it would help to partitionate the table every year?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 09:17 Richard Huxton <dev@archonet.com>
parent: Tobias Brox <tobias@nordicbet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2007-01-12 09:17 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-performance
Tobias Brox wrote:
> [Peter Childs - Fri at 08:56:54AM +0000]
>> Can you say what state might be rather than what it is not. I'm guess
>> that state is an int but there is only a limited list of possible
>> states, if you can say what it might be rather than what it is the
>> index is more liklly to be used.
>
> explain select * from events where state in (1,2,3) and event_time<now()
>
> also estimates almost 5k of rows.
Try a partial index:
CREATE INDEX my_new_index ON events (event_time)
WHERE state in (1,2,3);
Now, if that doesn't work you might want to split the query into two...
SELECT * FROM events
WHERE state IN (1,2,3) AND event_time < '2007-01-01'::date
UNION ALL
SELECT * FROM events
WHERE state IN (1,2,3) AND event_time >= '2007-01-01'::date AND
event_time < now();
CREATE INDEX my_new_index ON events (event_time)
WHERE state in (1,2,3) AND event_time < '2007-01-01'::date;
CREATE INDEX event_time_state_idx ON events (event_time, state);
You'll want to replace the index/update the query once a year/month etc.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 09:56 Tobias Brox <tobias@nordicbet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tobias Brox @ 2007-01-12 09:56 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tobias Brox <tobias@nordicbet.com>; Peter Childs <peterachilds@gmail.com>; pgsql-performance
[Richard Huxton - Fri at 09:17:48AM +0000]
> Try a partial index:
> CREATE INDEX my_new_index ON events (event_time)
> WHERE state in (1,2,3);
I have that, the index is used and the query is lightning fast - the
only problem is that the planner is using the wrong estimates. This
becomes a real problem when doing joins and more complex queries.
> Now, if that doesn't work you might want to split the query into two...
Hm, that's an idea - to use a two-pass query ... first:
select max(event_time) from events where state in (1,2,3);
and then use the result:
select * from events
where event_time>? and event_time<now() and state in (1,2,3)
This would allow the planner to get the estimates in the right ballpark
(given that the events won't stay for too long in the lower states), and
it would in any case not be significantly slower than the straight-ahead
approach - but quite inelegant.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 10:41 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Tobias Brox <tobias@nordicbet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Heikki Linnakangas @ 2007-01-12 10:41 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-performance
Tobias Brox wrote:
> Maybe it would help to partitionate the table every year?
I thought about partitioning the table by state, putting rows with
state=4 into one partition, and all others to another partition.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 10:44 Tobias Brox <tobias@nordicbet.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tobias Brox @ 2007-01-12 10:44 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Tobias Brox <tobias@nordicbet.com>; Peter Childs <peterachilds@gmail.com>; pgsql-performance
[Heikki Linnakangas - Fri at 10:41:34AM +0000]
> I thought about partitioning the table by state, putting rows with
> state=4 into one partition, and all others to another partition.
That sounds like a good idea - but wouldn't that be costly when changing state?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Planner statistics, correlations
@ 2007-01-12 10:55 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Tobias Brox <tobias@nordicbet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Heikki Linnakangas @ 2007-01-12 10:55 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-performance
Tobias Brox wrote:
> [Heikki Linnakangas - Fri at 10:41:34AM +0000]
>> I thought about partitioning the table by state, putting rows with
>> state=4 into one partition, and all others to another partition.
>
> That sounds like a good idea - but wouldn't that be costly when changing state?
In PostgreSQL, UPDATE internally inserts a new row and marks the old one
as deleted, so there shouldn't be much of a performance difference.
I'm not very familiar with our partitioning support, so I'm not sure if
there's any problems with an update moving a row from one partition to
another. I think you'll have to create an INSTEAD OF UPDATE rule to do a
DELETE on one partition and an INSERT on the other partition. Depending
on your application, that might be a problem; UPDATE is different from
DELETE+INSERT from transaction isolation point of view.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-01-12 10:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-12 08:16 Planner statistics, correlations Tobias Brox <tobias@nordicbet.com>
2007-01-12 08:56 ` Peter Childs <peterachilds@gmail.com>
2007-01-12 09:05 ` Tobias Brox <tobias@nordicbet.com>
2007-01-12 09:17 ` Richard Huxton <dev@archonet.com>
2007-01-12 09:56 ` Tobias Brox <tobias@nordicbet.com>
2007-01-12 10:41 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-01-12 10:44 ` Tobias Brox <tobias@nordicbet.com>
2007-01-12 10:55 ` Heikki Linnakangas <heikki@enterprisedb.com>
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