Hi,
I have come across a weird bug (i think) in postgres 8.1.11 (possibly
others)
Without going into my table structure detail I will demonstrate the
problem by showing the select statements:
The following statement:
SELECT count(*)
FROM object o, object_version v, object_type ot
where
Hey,
I have a table that links content together and it currently holds
about 17 mio records. Typical query is a join with a content table and
link table:
noovo-new=# explain analyze SELECT core_accessor.id,
core_accessor.content_type_id,
core_accessor.object_id, core_accessor.ordering,
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca tre...@gmail.com wrote:
Hey,
I have a table that links content together and it currently holds
about 17 mio records. Typical query is a join with a content table and
link table:
noovo-new=# explain analyze SELECT core_accessor.id,
But it's already attached in the first mail or am I missing something?
If you don't see it, check this: http://pastebin.com/d71b996d0
Sebastjan
On Tue, Mar 3, 2009 at 6:12 PM, Robert Haas robertmh...@gmail.com wrote:
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca tre...@gmail.com wrote:
On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Are you sure you are comparing apples to apples here? Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc? And are they running on the same hardware?
Thank you for
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca tre...@gmail.com wrote:
- Nested Loop (cost=0.00..31883.13 rows=2861 width=860)
(actual time=0.089..543.497 rows=68505 loops=1)
- Index Scan using core_accessor_fresh_idx on
core_accessor (cost=0.00..5460.07 rows=2970
On Tue, 3 Mar 2009, Aaron Guyon wrote:
On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Are you sure you are comparing apples to apples here? Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc? And are they
On Tue, Mar 3, 2009 at 12:20 PM, Sebastjan Trepca tre...@gmail.com wrote:
But it's already attached in the first mail or am I missing something?
If you don't see it, check this: http://pastebin.com/d71b996d0
Woops, sorry, I thought you had sent plain EXPLAIN. I see it now.
The lowest level
Set statistics to 1000, reanalyzed and got exactly same results:
noovo-new=# explain analyze SELECT core_accessor.id,
core_accessor.content_type_id,
core_accessor.object_id, core_accessor.ordering,
core_accessor.label, core_accessor.date_posted,
core_accessor.publish_state,
On Tue, Mar 3, 2009 at 12:38 PM, da...@lang.hm wrote:
if you haven't done a vaccum analyse on either installation then postgres'
idea of what sort of data is in the database is unpredictable, and as a
result it's not surprising that the two systems guess differently about what
sort of plan is
Still the same :/
I raised the default_statistics_target to 600 (it was already 100). I
then restarted pg, ran analyze through all tables and yet there is not
effect.
This is the output for core_accessor:
INFO: analyzing public.core_accessor
INFO: core_accessor: scanned 291230 of 291230 pages,
Maybe this is useful, I removed the JOIN and it uses other
index(core_accessor_date_idx indexes (date_posted, nooximity)), but
its still hardly any better:
noovo-new=# explain analyze SELECT * FROM core_accessor WHERE
((core_accessor.slot_type_id = 119
noovo-new(# AND core_accessor.slot_id = 472
On Tue, Mar 3, 2009 at 2:16 PM, Sebastjan Trepca tre...@gmail.com wrote:
Maybe this is useful, I removed the JOIN and it uses other
index(core_accessor_date_idx indexes (date_posted, nooximity)), but
its still hardly any better:
noovo-new=# explain analyze SELECT * FROM core_accessor WHERE
Sebastjan Trepca tre...@gmail.com writes:
It thinks there are even less rows in the set:
- Index Scan using core_accessor_fresh_idx on
core_accessor (cost=0.00..8955.44 rows=2440 width=92) (actual
time=0.056..53.107 rows=69312 loops=1)
Index Cond:
Aaron Guyon battlem...@gmail.com writes:
I find it telling that the query plan differs so much between postgres 8.2.
Well, you haven't shown us either the query or the table definitions,
so we're just guessing in the dark. However, the occurrences of
::numeric in the query plan make me wonder
Hello all
In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5 we
have a database with basically two kinds of transactions:
- short transactions with a couple of updates and inserts that runs all the
day;
- batch data loads with hundreds of inserts that runs several
On Tue, Mar 3, 2009 at 5:28 PM, Flavio Henrique Araque Gurgel
fla...@4linux.com.br wrote:
Hello all
In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5
we have a database with basically two kinds of transactions:
- short transactions with a couple of updates and
Tue, 3 Mar 2009 18:37:42 -0700 -n
Scott Marlowe scott.marl...@gmail.com írta:
Oh my lord, that is a foot gun waiting to go off. Assuming 2k
connections, and somehow a fair number of them went active with big
I absolutely agree with Scott. Plus set effective_cache_size
accordingly, this would
18 matches
Mail list logo
