Robert Haas writes:
> Yeah, I'm not familiar with the logic in that area of the code, so I
> can't comment all that intelligently. However, I feel like there's a
> class of things that could potentially be optimized if we know that
> the only snapshot they could affect is the one we're currently
On Sat, Nov 13, 2010 at 7:54 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane wrote:
>>> OK, this is an artifact of the "HOT update" optimization. Before
>>> creating the index, you did updates on the table that would have been
>>> executed differently if
Robert Haas writes:
> On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane wrote:
>> OK, this is an artifact of the "HOT update" optimization. Before
>> creating the index, you did updates on the table that would have been
>> executed differently if the index had existed. When the index does get
>> creat
On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane wrote:
> Jon Nelson writes:
>> OK. This is a highly distilled example that shows the behavior.
>
>> BEGIN;
>> CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b,
>> ''::text AS c from generate_series(1,500) AS x;
>> UPDATE foo SET c = 'fo
Jon Nelson writes:
> What would the effect be of patching postgresql to allow indexes to
> see and follow the HOT chains during index creation?
It would break things. We did a *lot* of thinking about this when
HOT was implemented; there are not simple improvements to be made.
The particular cas
On Sat, Nov 13, 2010 at 9:41 AM, Tom Lane wrote:
> Jon Nelson writes:
>> OK. This is a highly distilled example that shows the behavior.
>
>> BEGIN;
>> CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b,
>> ''::text AS c from generate_series(1,500) AS x;
>> UPDATE foo SET c = 'foo
Jon Nelson writes:
> OK. This is a highly distilled example that shows the behavior.
> BEGIN;
> CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b,
> ''::text AS c from generate_series(1,500) AS x;
> UPDATE foo SET c = 'foo' WHERE b = 'A' ;
> CREATE INDEX foo_b_idx on foo (b);
> [
On Thu, Oct 28, 2010 at 9:23 AM, Tom Lane wrote:
> Jon Nelson writes:
>> On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane wrote:
>>> It thinks it's faster, or there is some reason why it *can't* use the
>>> index, like a datatype mismatch. You could tell which by trying "set
>>> enable_seqscan = off"
On Wed, Oct 27, 2010 at 3:44 PM, Justin Pitts wrote:
>> Jason Pitts:
>> RE: changing default_statistics_target (or via ALTER TABLE SET STATS)
>> not taking effect until ANALYZE is performed.
>>
>> I did already know that, but it's probably good to put into this
>> thread. However, you'll note that
> Jason Pitts:
> RE: changing default_statistics_target (or via ALTER TABLE SET STATS)
> not taking effect until ANALYZE is performed.
>
> I did already know that, but it's probably good to put into this
> thread. However, you'll note that this is a temporary table created at
> the beginning of a t
If you alter the default_statistics_target or any of the specific
statistics targets ( via ALTER TABLE SET STATISTICS ) , the change
will not have an effect until an analyze is performed.
This is implied by
http://www.postgresql.org/docs/9.0/static/planner-stats.html and
http://www.postgresql.org/
Jon Nelson writes:
> On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane wrote:
>> It thinks it's faster, or there is some reason why it *can't* use the
>> index, like a datatype mismatch. Â You could tell which by trying "set
>> enable_seqscan = off" to see if that will make it change to another
>> plan;
On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane wrote:
> Jon Nelson writes:
>> I'd like to zoom out a little bit and, instead of focusing on the
>> specifics, ask more general questions:
..
>> - is there some way for me to determine /why/ the planner chooses a
>> sequential scan over other options?
>
>
Jon Nelson writes:
> I'd like to zoom out a little bit and, instead of focusing on the
> specifics, ask more general questions:
> - does the table being temporary effect anything? Another lister
> emailed me and wondered if ANALYZE on a temporary table might behave
> differently.
Well, the autov
On Wed, Oct 27, 2010 at 4:45 PM, Tom Lane wrote:
> Jon Nelson writes:
>> The sequence goes exactly like this:
>
>> BEGIN;
>> CREATE TEMPORARY TABLE (20 columns, mostly text, a few int).
>> COPY (approx 8 million rows, ~900 MB)[1]
>> UPDATE (2.8 million of the rows)
>> UPDATE (7 rows)
>> UPDATE (2
Jon Nelson writes:
> The sequence goes exactly like this:
> BEGIN;
> CREATE TEMPORARY TABLE (20 columns, mostly text, a few int).
> COPY (approx 8 million rows, ~900 MB)[1]
> UPDATE (2.8 million of the rows)
> UPDATE (7 rows)
> UPDATE (250 rows)
> UPDATE (3500 rows)
> UPDATE (3100 rows)
> a bunch
On Wed, Oct 27, 2010 at 2:43 PM, Tom Lane wrote:
> Jon Nelson writes:
>> The most recent experiment shows me that, unless I create whatever
>> indexes I would like to see used *before* the large (first) update,
>> then they just don't get used. At all.
>
> You're making a whole lot of assertions
Jon Nelson writes:
> The most recent experiment shows me that, unless I create whatever
> indexes I would like to see used *before* the large (first) update,
> then they just don't get used. At all.
You're making a whole lot of assertions here that don't square with
usual experience. I think the
On Wed, Oct 27, 2010 at 1:52 PM, Jon Nelson wrote:
> On Wed, Oct 27, 2010 at 1:32 PM, Reid Thompson wrote:
>> On Wed, 2010-10-27 at 13:23 -0500, Jon Nelson wrote:
>>> set it to 500 and restarted postgres.
>>
>> did you re-analyze?
>
> Not recently. I tried that, initially, and there was no improv
On Wed, Oct 27, 2010 at 1:32 PM, Reid Thompson wrote:
> On Wed, 2010-10-27 at 13:23 -0500, Jon Nelson wrote:
>> set it to 500 and restarted postgres.
>
> did you re-analyze?
Not recently. I tried that, initially, and there was no improvement.
I'll try it again now that I've set the stats to 500.
On Wed, 2010-10-27 at 13:23 -0500, Jon Nelson wrote:
> set it to 500 and restarted postgres.
did you re-analyze?
On Wed, Oct 27, 2010 at 12:59 PM, Jon Nelson wrote:
> On Wed, Oct 27, 2010 at 12:44 PM, Mladen Gogala
> wrote:
>> On 10/27/2010 1:29 PM, Jon Nelson wrote:
>> How big is your default statistics target? The default is rather small, it
>> doesn't produce very good or usable histograms.
>
> Currently
On Wed, Oct 27, 2010 at 12:44 PM, Mladen Gogala
wrote:
> On 10/27/2010 1:29 PM, Jon Nelson wrote:
> How big is your default statistics target? The default is rather small, it
> doesn't produce very good or usable histograms.
Currently, default_statistics_target is 50.
I note that if I create a i
On 10/27/2010 1:29 PM, Jon Nelson wrote:
I have an app which imports a lot of data into a temporary table, does
a number of updates, creates some indexes, and then does a bunch more
updates and deletes, and then eventually inserts some of the columns
from the transformed table into a permanent ta
G u i d o B a r o s i o wrote:
The box:
Linux 2.4.24-ck1
8 Intel(R) Xeon(TM) MP CPU 2.80GHz
4 gb RAM.
Postgresql 7.4.2
The problem:
Short in disk space. (waiting new hard)
The real problem:
Developers usually write queries involving the creation of temporary tables.
I seen too this behavior,
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
G u i d o B a r o s i o wrote:
| The box:
| Linux 2.4.24-ck1
| 8 Intel(R) Xeon(TM) MP CPU 2.80GHz
| 4 gb RAM.
| Postgresql 7.4.2
|
| The problem:
| Short in disk space. (waiting new hard)
|
| The real problem:
| Developers usually write queries involvi
The box:
Linux 2.4.24-ck1
8 Intel(R) Xeon(TM) MP CPU 2.80GHz
4 gb RAM.
Postgresql 7.4.2
The problem:
Short in disk space. (waiting new hard)
The real problem:
Developers usually write queries involving the creation of temporary tables.
The BF question:
Is a good idea to link this tmp tables
27 matches
Mail list logo
