So, I defragged my disk and reran my original query and it got a little
better, but still far higher than I'd like. I then rebuilt (dropped and
recreated) the ad_log_date_all index and reran the query and it is quite a
bit better:
# explain analyze select * from ad_log where date(start_time) <
(I didn't notice that I ended up with "score.score" in this test case. Oops.)
2009/4/8 Віталій Тимчишин :
> How about
>
> select s1.*
> from score s1 join score s2 on s1.game_id=s2.game_id and s2.score >=
> s1.score
> group by s1.*
> having count(s2.*) <= N
I can see what this is doing, but I'm
2009/4/9 Glenn Maynard
> (This is related to an earlier post on -sql.)
>
> I'm querying for the N high scores for each game, with two tables:
> scores and games.
>
> CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
> CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
> game_id INT
(This is related to an earlier post on -sql.)
I'm querying for the N high scores for each game, with two tables:
scores and games.
CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
game_id INTEGER REFERENCES game (id));
-- test dat
Hi,
Ok I need to answer some more :)
Le 8 avr. 09 à 20:20, Jeff a écrit :
To add a table with a pk you edit slon_tools.conf and add something
along the lines of:
"someset" => {
"set_id" => 5,
"table_id" => 5,
"pkeyedtables" => [ "tacos", "burritos", "gorditas" ]
}
th
On Apr 7, 2009, at 1:18 PM, Andrew Sullivan wrote:
I should have stated that differently. First, you're right that if
you don't know where to look or what to look for, you can easily be
unaware of nodes being out of sync. What's not a problem with Slony
_$cluster.sl_status on the origin is
Marinos Yannikos writes:
> Marinos Yannikos wrote:
>> (what exactly does ANALYZE look at for text columns? in our case, about
>> 7% of the rows match the index condition, so it seems that left-anchored
>> regexp/like matches are not evaluated using the gathered
>> most-common-value list at all)
On Wed, Apr 8, 2009 at 9:42 AM, Marinos Yannikos wrote:
> It seems that ANALYZE does not really sample text column values as much as
> it could. We have some very bad query plans resulting from this:
>
> ...
> -> Bitmap Index Scan on m_pkey (cost=0.00..28.61 rows=102 width=0)
> (actual t
Marinos Yannikos wrote:
(what exactly does ANALYZE look at for text columns? in our case, about
7% of the rows match the index condition, so it seems that left-anchored
regexp/like matches are not evaluated using the gathered
most-common-value list at all)
oops, I think I gave myself the answ
It seems that ANALYZE does not really sample text column values as much
as it could. We have some very bad query plans resulting from this:
...
-> Bitmap Index Scan on m_pkey (cost=0.00..28.61 rows=102
width=0) (actual time=171.824..171.824 rows=683923 loops=1)
Index
Heikki Linnakangas wrote:
Lists wrote:
Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1
for most data, 1 for wal and a few tables and indexes)
In total all databases on the server are about 10G on disk (about 2GB
in pgdump format).
I'd suggest buying as much RAM as you
On Tue, 7 Apr 2009, Tom Lane wrote:
Subsequent discussion showed that the problem was Matthew hadn't found
that page. I guess that at least the DECLARE CURSOR reference page
ought to have something like "if you are trying to use cursors in
plpgsql, see ". Matthew, where *were* you looking exact
Andrew Sullivan wrote:
I should have stated that differently. First, you're right that if
you don't know where to look or what to look for, you can easily be
unaware of nodes being out of sync. What's not a problem with Slony
is that the nodes can get out of internally consistent sync state: i
13 matches
Mail list logo
