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
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.
>
>
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,
>
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 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 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
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
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,
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
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, 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
"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
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 ...
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
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
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:
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.
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
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
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
[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
21 matches
Mail list logo
