[Sorry if you receive multiple copies of this message.]
[Please feel free to forward the message to others who may be
interested.]
Hi,
We are a computer systems research group at the Computer Science
department at Rutgers University, and are conducting research on
simplifying the software conf
Hello,
I'm using postgresql 8.1.5. Sorry if this is not the right area to ask this. I
already have command string turned on at the postgresql.conf , and am currently
trying to troubleshoot some connection problem at a server that is causing
performance issues. Apart from "" and the specific SQL
I've got a table set up with an XML field that I would like to search on with
2.5 million records. The xml are serialized objects from my application
which are too complex to break out into separate tables. I'm trying to run a
query similar to this:
SELECT serialized_object as outVal
Hi:
Looks like after postgres db server reboot, first query is very slow
(10+mins). After the system cache built, query is pretty fast.
Now the question is how to speed up the first query slow issue?
Any pointers?
Thanks
wei
Would love to get some advice on how to change my conf settings / setup
to get better I/O performance.
Server Specs:
2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)
4GB RAM
4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored )
FreeBSD 6.4
Apache 2.2
PostgreSQL 8.3.
Yep I ran into the exact same problem.
My solution was to create a pl/pgsql function to query the child tables: (
http://archives.postgresql.org/pgsql-performance/2008-11/msg00284.php)
If you find a better solution please share.
-Greg Jaman
On Wed, Sep 2, 2009 at 1:15 PM, Kenneth Cox wrote:
With postgresql-8.3.6, I have many partitions inheriting a table. SELECT
min() on the parent performs a Seq Scan, but SELECT min() on a child uses
the index. Is this another case where the planner is not aware enough to
come up with the best plan? I tried creating an index on the parent ta
On 9/2/09 8:59 AM, "Joshua D. Drake" wrote:
> On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:
>
>>>
>>> You sure you remembered those fiddly little casts everywhere?
>>> (Frankly, declaring "time" as integer and not timestamp here strikes
>>> me as utter lunacy.) What PG version are
Kevin Kempter writes:
> In any case I ran the exact same query as you and it still scans most (but
> not
> all) partitions.
AFAICT it's scanning the right partitions in this example. What's
different in the case where it scans all?
> Were on version
This seems to have got truncated ...
"Joshua D. Drake" writes:
> As far as I know constraint exclusion doesn't work with date_part or
> extract().
Uh, you clipped the example in my message showing that it does,
at least in the particular case Kevin showed us.
There are some variants of date_part that aren't immutable, but timestamp
On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:
> >
> > You sure you remembered those fiddly little casts everywhere?
> > (Frankly, declaring "time" as integer and not timestamp here strikes
> > me as utter lunacy.) What PG version are you using?
> >
> > regards, tom l
On Wednesday 02 September 2009 09:19:20 Tom Lane wrote:
> Kevin Kempter writes:
> > I cant figure out why we're scanning all of our partitions.
>
> The example works as expected for me:
>
> regression=# CREATE TABLE url_hits (
> id integer NOT NULL,
> content_type_id integer,
> file_ex
On Wed, Sep 2, 2009 at 4:05 PM, Kevin Kempter wrote:
> explain select * from pwreport.url_hits where "time" > extract('epoch' from
> timestamp '2009-08-12 00:00:00')::int4;
>
Hm. Actually I would have thought this would work. You're using
"timestamp" which defaults to without timezone and
date_par
Kevin Kempter writes:
> I cant figure out why we're scanning all of our partitions.
The example works as expected for me:
regression=# CREATE TABLE url_hits (
id integer NOT NULL,
content_type_id integer,
file_extension_id integer,
"time" integer,
bytes integer NOT NULL,
Check the caveats at
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
"Constraint exclusion only works when the query's WHERE clause contains
constants. A parameterized query will not be optimized, since the planner
cannot know which partitions the parameter value might select a
On Wed, Sep 2, 2009 at 8:05 AM, Kevin Kempter wrote:
>
> > > the explain plan shows most any query scans/hits all partitions even if
> > > we specify the partition key:
> > >
> > > explain select * from pwreport.url_hits where "time" >
> > > date_part('epoch'::text, '2009-08-12'::timestamp without
On Wednesday 02 September 2009 08:55:38 Kenneth Marshall wrote:
> The planner does not yet work as efficiently as it could
> with child tables. Check the recent mail archives for a
> long discussion of the same.
>
> Regards,
> Ken
>
> On Wed, Sep 02, 2009 at 08:52:30AM -0600, Kevin Kempter wrote:
>
On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
> On Wed, Sep 2, 2009 at 8:52 AM, Kevin Kempter
wrote:
> > Hi all;
> >
> > I cant figure out why we're scanning all of our partitions.
> >
> > We setup our tables like this:
> >
> >
> > Base Table:
> >
> > CREATE TABLE url_hits (
> >
On Wed, Sep 2, 2009 at 8:52 AM, Kevin Kempter wrote:
> Hi all;
>
> I cant figure out why we're scanning all of our partitions.
>
> We setup our tables like this:
>
>
> Base Table:
>
> CREATE TABLE url_hits (
> id integer NOT NULL,
> content_type_id integer,
> file_extension_id integer,
>
The planner does not yet work as efficiently as it could
with child tables. Check the recent mail archives for a
long discussion of the same.
Regards,
Ken
On Wed, Sep 02, 2009 at 08:52:30AM -0600, Kevin Kempter wrote:
> Hi all;
>
> I cant figure out why we're scanning all of our partitions.
>
>
Hi all;
I cant figure out why we're scanning all of our partitions.
We setup our tables like this:
Base Table:
CREATE TABLE url_hits (
id integer NOT NULL,
content_type_id integer,
file_extension_id integer,
"time" integer,
bytes integer NOT NULL,
path_id integer,
p
21 matches
Mail list logo
