On Fri, May 22, 2015 at 3:42 PM, Tom Lane wrote:
> Dave Johansen writes:
> > I'm trying to call MAX() on the first value of a multi-column index of a
> > partitioned table and the planner is choosing to do a sequential scan
> > instead of an index scan. Is there some
I'm trying to call MAX() on the first value of a multi-column index of a
partitioned table and the planner is choosing to do a sequential scan
instead of an index scan. Is there something I can do to fix this?
Here's a simplified version of our schema:
CREATE TABLE data ( tutci DOUBLE PRECISION, t
On Mon, Nov 17, 2014 at 8:13 AM, Tom Lane wrote:
> Magnus Hagander writes:
> > On Oct 16, 2014 12:58 AM, "Tom Lane" wrote:
> >> That is in fact exactly what people pay Red Hat to do, and it was my job
> >> to do it for Postgres when I worked there. I don't work there any more,
> >> but I'm sur
On Wed, Oct 15, 2014 at 3:25 PM, Josh Berkus wrote:
> On 10/15/2014 01:19 PM, Dave Johansen wrote:
> > Sorry I don't understand what you mean by that. My understanding is that
> > RedHat maintains fixes for security and other major issues for packages
> > that have be
On Wed, Oct 15, 2014 at 1:36 PM, Igor Neyman wrote:
>
>
>
>
> *From:* Dave Johansen [mailto:davejohan...@gmail.com]
> *Sent:* Wednesday, October 15, 2014 4:20 PM
> *To:* Igor Neyman
> *Cc:* Josh Berkus; pgsql-performance
> *Subject:* Re: [PERFORM] Partitions and work_
On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman wrote:
>
>
>
>
> *From:* pgsql-performance-ow...@postgresql.org [mailto:
> pgsql-performance-ow...@postgresql.org] *On Behalf Of *Dave Johansen
> *Sent:* Wednesday, October 15, 2014 4:05 PM
> *To:* Josh Berkus
> *Cc:* pgsql-
On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus wrote:
> On 10/14/2014 10:08 AM, Dave Johansen wrote:
> > I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> > work_mem and partitions interact.
> >
> > https://wiki.postgresql.org/wiki/Tuning
I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
work_mem and partitions interact.
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
The above wiki states that "if a query involves doing merge sorts of 8
tables, that requires 8 times work_mem." If I ha
I'm working with a large database using 8.4 that's partitioned on 4 week
boundaries and when I use a prepared statement that limits by time as one
of the bind parameters the planner seems to not select just the partitions
of interest but wants to scan all of them. From reading the documentation
of
On Thu, Jan 2, 2014 at 12:36 PM, Dave Johansen wrote:
> On Thu, Jan 2, 2014 at 11:39 AM, Dave Johansen wrote:
>
>> On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
>>
>>> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen
>>> wrote:
>>>
>>>
On Thu, Jan 2, 2014 at 11:39 AM, Dave Johansen wrote:
> On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
>
>> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen wrote:
>>
>>> I just ran into an interesting issue on Postgres 8.4. I have a database
>>> with
On Fri, Dec 20, 2013 at 10:46 PM, David Rowley wrote:
> On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen wrote:
>
>> I just ran into an interesting issue on Postgres 8.4. I have a database
>> with about 3 months of data and when I do following query:
>> SELECT DATE_TRUN
On Fri, Dec 20, 2013 at 8:52 AM, Dave Johansen wrote:
> On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe
> wrote:
>
>> On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen
>> wrote:
>> >>
>> > On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe
>> >
On Fri, Dec 20, 2013 at 9:18 AM, Shaun Thomas wrote:
> On 12/20/2013 09:59 AM, Alvaro Herrera wrote:
>
> WITH moved AS (
>> DELETE FROM src WHERE ..
>> RETURNING *
>> ) INSERT INTO dst SELECT * FROM moved;
>>
>
> I know that's effectively an atomic action, but I'd feel a lot more
On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe wrote:
> On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen
> wrote:
> >>
> > On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe
> > wrote:
> >> I'll add that you can use assymetric partitioning if you tend to do a
On Fri, Dec 20, 2013 at 8:22 AM, Kevin Grittner wrote:
> Dave Johansen wrote:
>
> > Is there a benefit to having the WAL and logs on the separate
> > RAID 1? Or is just having them be part of the larger RAID 1+0
> > just as good?
>
> I once accidentally left t
On Fri, Dec 20, 2013 at 7:10 AM, Shaun Thomas wrote:
> On 12/19/2013 04:06 PM, Dave Johansen wrote:
>
> Right now, we're running a RAID 1 for pg_clog, pg_log and pg_xlog and
>> then a RAID 1+0 with 12 disks for the data. Would there be any benefit
>> to running
I just ran into an interesting issue on Postgres 8.4. I have a database
with about 3 months of data and when I do following query:
SELECT DATE_TRUNC('day', time) AS time_t, COUNT(*) FROM mytable GROUP BY
time_t;
EXPLAIN shows that it's doing a sort and then a GroupAggregate. There will
only be ~90
On Thu, Dec 19, 2013 at 10:44 AM, Shaun Thomas wrote:
> On 12/19/2013 11:00 AM, Dave Johansen wrote:
>
> When I run pgbench in "SELECT only" after doing "-i -s 2000" I get what
>> appears to be good performance (60k-70k tps) but if I initialize a new
>>
I'm working on setting up a large database (or at least what I consider to
be a large one with several tables having 10-20 million records inserted
per day), and I've been using pgbench to verify that the hardware and
database are configured in an optimal manner.
When I run pgbench in "SELECT only
Sorry for the delay response. We had some hardware/configuration issues
that appear to be solved now, so now we're starting to actually play with
modifying the database.
On Sat, Dec 7, 2013 at 1:29 PM, Jeff Janes wrote:
> On Thu, Dec 5, 2013 at 7:36 AM, Dave Johansen wrote:
>
>&g
I'm managing a database that is adding about 10-20M records per day to a
table and time is a core part of most queries, so I've been looking into
seeing if I need to start using partitioning based on the time column and
I've found these general guidelines:
Don't use more than about 50 paritions (
On Thu, Jun 27, 2013 at 10:34 AM, bricklen wrote:
> On Thu, Jun 27, 2013 at 10:17 AM, Shaun Thomas
> wrote:
>
>>
>> Well yeah. That's not really the point, though. Aside from existing code,
>> hard-coding is generally frowned upon. Our devs have been using
>> CURRENT_DATE and its ilk for over si
On Fri, Apr 5, 2013 at 11:40 AM, Merlin Moncure wrote:
>
> On Fri, Apr 5, 2013 at 11:54 AM, Dave Johansen wrote:
> > On Sat, Feb 26, 2011 at 2:38 PM, Dave Johansen
> > wrote:
> >>
> >> Unfortunately, I'm running 8.3.3 and to my knowledge the windowing s
On Sat, Feb 26, 2011 at 2:38 PM, Dave Johansen
wrote:
>
> Unfortunately, I'm running 8.3.3 and to my knowledge the windowing stuff
> wasn't added til 8.4.
> Dave
>
> On Feb 26, 2011 2:06 PM, "Josh Berkus" wrote:
> > Dave,
> >
> > Why n
On Wed, May 18, 2011 at 8:54 AM, Robert Klemme
wrote:
> On Wed, May 18, 2011 at 5:26 PM, Dave Johansen
> wrote:
> > I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
> > tables but when I do a select on the view using a LIMIT, it scans the
> entire
getting it moved over to a machine that does or getting it
the info onto a machine where I can post the pertinent information about the
schema and explain outputs.
Thanks,
Dave
--
Dave Johansen
phone: (520) 302-4526
On Sun, May 22, 2011 at 10:34 AM, Robert Klemme
wrote:
> Dave,
>
> how o
I am using Postgres 8.3 and I have an issue very closely related to the one
described here:
http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
select on the view using a LIMIT, it scans the entire tables and
I am using Postgres 8.3 and I have an issue very closely related to the one
described here:
http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
select on the view using a LIMIT, it scans the entire tables and
I am using Postgres 8.3.3 and I have a VIEW which is a UNION ALL of two
tables but when I do a select on the view using a LIMIT, it scans the entire
tables and takes significantly longer than writing out the query with the
LIMITs in the sub-queries themselves. Is there a solution to get the view to
On Wed, May 18, 2011 at 1:46 AM, Clemens Eisserer wrote:
> Hi,
>
> Does anybody know why the planner treats "= ANY(ARRAY(select ...))"
> differently than "IN(select ...)"?
> Which one is preferable, when I already have a lot of joins?
>
> Thanks, Clemens
>
> 2011/5/17 Clemens Eisserer :
> > Hi,
>
On Mon, May 16, 2011 at 3:30 PM, Clemens Eisserer wrote:
> Hi,
>
> I have a quite complex, performance sensitive query in a system with a
> few (7) joins:
> select from t1 left join t2 WHERE id IN (select )
>
> For this query the planner evaluates the IN with a hash semi join last,
>
Unfortunately, I'm running 8.3.3 and to my knowledge the windowing stuff
wasn't added til 8.4.
Dave
On Feb 26, 2011 2:06 PM, "Josh Berkus" wrote:
> Dave,
>
> Why not test the windowing version I posted?
>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
On Fri, Feb 25, 2011 at 1:45 PM, Dave Crooke wrote:
> Hi Dave
>
> Yes, 100% the best solution I did the same thing a while back, I just
> have a separate copy of the data in a "latest" table and the Java code just
> runs a second SQL statement to update it when writing a new record (I've
> n
On Thu, Feb 24, 2011 at 4:38 PM, Dave Crooke wrote:
> Thanks to all I had a tickling feeling at the back of my mind that
> there was a neater answer here. For the record, times (all from in-memory
> cached data, averaged over a bunch of runs):
>
> Dependent subquery = 117.9 seconds
> Join to
On Thu, Feb 24, 2011 at 12:59 PM, Vik Reykja wrote:
> On Thu, Feb 24, 2011 at 20:56, Dave Johansen wrote:
>
>> On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja wrote:
>>
>>> On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
>>>
>>>> You'll no
I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on
two joins and it doesn't seem to want to push the IN (subquery)
optimization down into the plan for the two queries being unioned. Is
there something I can do to fix this? Or is it just a limitation of
the planner/optimizer?
I a
On Thu, Feb 24, 2011 at 12:33 PM, Vik Reykja wrote:
> On Thu, Feb 24, 2011 at 16:14, Dave Johansen wrote:
>
>> You'll notice that the subquery version is doing the full join and then
>> the filtering, but the explicitly listed version pushing the filtering into
>>
On Thu, Feb 24, 2011 at 8:14 AM, Dave Johansen wrote:
> I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on two
> joins and it doesn't seem to want to push the IN (subquery) optimization
> down into the plan for the two queries being unioned. Is there somethin
I'm using PostgreSQL 8.3.3 and I have a view that does a UNION ALL on two
joins and it doesn't seem to want to push the IN (subquery) optimization
down into the plan for the two queries being unioned. Is there something I
can do to fix this? Or is it just a limitation of the planner/optimizer?
I a
40 matches
Mail list logo
