Alexander Staubo wrote:
> On Friday, February 22, 2013 at 21:47 , Kevin Grittner wrote:
>> In my experience these problems come largely from the planner
>> not knowing the cost of dealing with each tuple. I see a lot
>> less of this if I raise cpu_tuple_cost to something in the 0.03
>> to 0.05 ra
On Friday, February 22, 2013 at 21:47 , Kevin Grittner wrote:
> I suspect you would be better off without those two indexes, and
> instead having an index on (conversation_id, created_at). Not just
> for the query you show, but in general.
Indeed, that solved it, thanks!
> In my experience th
On Friday, February 22, 2013 at 21:33 , Tom Lane wrote:
> The reason is that the LIMIT may stop the query before it's scanned all
> of the index. The planner estimates on the assumption that the desired
> rows are roughly uniformly distributed within the created_at index, and
> on that assumption,
Alexander Staubo wrote:
> This is my schema:
>
> create table comments (
> id serial primary key,
> conversation_id integer,
> created_at timestamp
> );
> create index comments_conversation_id_index on comments (conversation_id);
> create index comments_created_at_index on com
Alexander Staubo writes:
>select comments.id from comments where
> conversation_id = 3975979 order by created_at limit 13
> I'm at a loss how to explain why the planner thinks scanning a huge
> index that covers the entire table will ever beat scanning a small index
> that has 17% of the
>> Also, you might could try clustering newly created tables on session_id
and setting the fillfactor down so rows with the same session id will stick
together on disk.
<<
My understanding of PG's cluster is that this is a one-time command that
creates a re-ordered table and doesn't maintain
On Fri, Feb 22, 2013 at 9:59 AM, Vitalii Tymchyshyn wrote:
>> Tuning Postgre is not an option, as the instance
>> is provided by Heroku and as far as I know cannot be tuned by me.
>>
> Most tuning parameters can be set at per-query basis, so you can issue
> alter database set param=value
> to have
I can't really help, but I can make it more clear why postgres is choosing
a _bitmap_ index scan rather than a regular index scan. With a regular
index scan it pumps the index for the locations of the rows that it points
to and loads those rows as it finds them. This works great if the rows in
th
I have a problem with a query that is planned wrong. This is my schema:
create table comments (
id serial primary key,
conversation_id integer,
created_at timestamp
);
create index comments_conversation_id_index on comments (conversation_id);
create index comments_create
Am Freitag, 22. Februar 2013, 14:35:25 schrieb Heikki Linnakangas:
> On 22.02.2013 10:25, Markus Schulz wrote:
> > i can run the query four times with good performance and after that
> > postgresql starts with the strange lseek() behavior.
>
> By default, the JDBC driver re-plans the prepared stat
2013/2/22 jackrg
> Tuning Postgre is not an option, as the instance
> is provided by Heroku and as far as I know cannot be tuned by me.
>
> Most tuning parameters can be set at per-query basis, so you can issue
alter database set param=value
to have same effect as if it was set through postgresql
A cool idea, but if I understand it correctly very specific and fussy. New
DB's are spawned on this model, and all the developers would have to be
aware of this non-standard behaviour, and DBA"s would have to create these
indexes every month, for every DB (as the log tables are created every
month)
Hi Jeff, thanks for the reply.
<<
What is going on during the interregnum? Whatever it is, it seems to be
driving the log_2013_01_session_idx index out of the cache, but not the
log_2013_01 table. (Or perhaps the table visit is getting the benefit of
effective_io_concurrency?)
.
Rebuilding
On Fri, Feb 22, 2013 at 8:36 AM, jackrg wrote:
> The following query produces a Recheck Cond and a costly Bitmap Heap Scan
> even though I have a composite index that covers both columns being
> filtered
> and selected.
Can you show us the definition of that index?
> I believe this is because
The following query produces a Recheck Cond and a costly Bitmap Heap Scan
even though I have a composite index that covers both columns being filtered
and selected. I believe this is because the initial bitmap scan produces
2912 rows, which is too many for the available bitmap space. I've tried
r
Markus,
* Markus Schulz (m...@antzsystem.de) wrote:
> as you can see the query runs fine.
> I can run this query from a bash-psql-while-loop/jdbc-cli-tool
> endless without any problems.
> so far so good.
[...]
> JBoss EAP 5.1.2 with connection pooling and xa-datasource/two-phase-commits
> (tr
On 22.02.2013 10:25, Markus Schulz wrote:
i can run the query four times with good performance and after that postgresql
starts with the strange lseek() behavior.
By default, the JDBC driver re-plans the prepared statement for the
first 4 invocations of the query. On the fifth invocation, it s
hello,
i have a strange and reproducible bug with some select queries and 64bit
postgresql builds (works fine on 32bit builds).
The postgres process will run with 100% cpu-load (no io-wait) and strace
will show endless lseek(..., SEEK_END) calls on one table for minutes.
lseek(28, 0, SEEK_END)
18 matches
Mail list logo
