>
>
>>
>> Setting enable_seqscan=off takes one of the shorter queries I was working
>> with from about 3 minutes to 300ms. This is a comparable performance
>> improvement to where I put a materialized view (with indexes) on top of the
>> materialized views instead of using a simple view on top of
On Tue, Feb 6, 2018 at 3:02 PM, Rick Otten wrote:
> Ooo. I wasn't aware of that option. (Learn something new every day!)
>
> Setting enable_seqscan=off takes one of the shorter queries I was working
> with from about 3 minutes to 300ms. This is a comparable performance
> improvement to where
Ooo. I wasn't aware of that option. (Learn something new every day!)
Setting enable_seqscan=off takes one of the shorter queries I was working
with from about 3 minutes to 300ms. This is a comparable performance
improvement to where I put a materialized view (with indexes) on top of the
materi
On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote:
> On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane wrote:
>
> > Rick Otten writes:
> > > I'm wrestling with a very similar problem too - except instead of
> > official
> > > partitions I have a views on top of a bunch (50+) of unioned material
On Sun, Feb 04, 2018 at 05:28:52PM +0200, Mariel Cherkassky wrote:
> I read those two links and I dont think that they are relevant because : 1
> 1)I didnt do any join.
> 2)I used a where clause in my select
https://www.postgresql.org/docs/current/static/ddl-partitioning.html
|The following caveat
On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane wrote:
> Rick Otten writes:
> > I'm wrestling with a very similar problem too - except instead of
> official
> > partitions I have a views on top of a bunch (50+) of unioned materialized
> > views, each "partition" with 10M - 100M rows. On 9.6.6 the que
Hi Tom,
Did you hear about any solution that is similar to oracle`s global index ?
Is there any way to query all the partitions with one index?
2018-02-04 17:39 GMT+02:00 Tom Lane :
> Mariel Cherkassky writes:
> > Great, it solved the issue. Seems problematic that the planner do full
> > scans o
Mariel Cherkassky writes:
> Great, it solved the issue. Seems problematic that the planner do full
> scans on all partitions in the first case isnt it ? Seems like a bug ?
to_date isn't an immutable function (it depends on timezone and possibly
some other GUC settings). So there's a limited amou
Rick Otten writes:
> I'm wrestling with a very similar problem too - except instead of official
> partitions I have a views on top of a bunch (50+) of unioned materialized
> views, each "partition" with 10M - 100M rows. On 9.6.6 the queries would
> use the indexes on each materialized view. On 1
I read those two links and I dont think that they are relevant because : 1
1)I didnt do any join.
2)I used a where clause in my select
2018-02-04 17:25 GMT+02:00 Justin Pryzby :
> On Sun, Feb 04, 2018 at 05:06:38PM +0200, Mariel Cherkassky wrote:
> > Great, it solved the issue. Seems problemati
On Sun, Feb 04, 2018 at 05:06:38PM +0200, Mariel Cherkassky wrote:
> Great, it solved the issue. Seems problematic that the planner do full
> scans on all partitions in the first case isnt it ? Seems like a bug ?
See also:
https://www.postgresql.org/message-id/20170725131650.GA30519%40telsasoft.co
Great, it solved the issue. Seems problematic that the planner do full
scans on all partitions in the first case isnt it ? Seems like a bug ?
2018-02-04 16:54 GMT+02:00 Andreas Kretschmer :
>
>
> Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
>
>> I checked the plan of the next query :
>> expl
Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
I checked the plan of the next query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
can you rewrite the query to
... where end_date between '2017/12/03'
Output of explain analyze :
explain analyze select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/04','YY/MM/DD');
QUERY PLAN
--
explain analyze takes too much time.. hours ...
I run it now but it will take some time.
The output of the explain :
Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8)
-> Gather (cost=38058211.16..38058211.37 rows=2 width=8)
Workers Planned: 2
-> Partial Agg
On Sun, Feb 4, 2018 at 8:19 AM, legrand legrand wrote:
> What is the value of guc constrain_exclusion ?
>
>
>
In my use case, which is a big union all behind a view, setting this to
off, on, or partition makes no difference. It still sequence scans all of
the sub-tables in pg 10.1 whereas it use
Explain analyse
Output ?
--
Sent from:
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
show constraint_exclusion;
constraint_exclusion
--
partition
(1 row)
2018-02-04 15:19 GMT+02:00 legrand legrand :
> What is the value of guc constrain_exclusion ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
What is the value of guc constrain_exclusion ?
--
Sent from:
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Mybe I wasnt clear. I'm having a 2 layers patitions mechanism :
My main table is called log_full :
CREATE TABLE log_full (a text,b text,c text, start_stop text, end_Date
date) partition range by (end_date))
Every day I create a partition that represent data from that day :
create table log_full_04
On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
>
> Hi,
> I configured range partitions on a date column of my main table(log_full).
> Each partition represents a day in the month. Every day partition has a
> list parition of 4 tables on a text column.
>
> l
On 02/04/2018 11:14 AM, Mariel Cherkassky wrote:
>
> Hi,
> I configured range partitions on a date column of my main
> table(log_full). Each partition represents a day in the month. Every day
> partition has a list parition of 4 tables on a text column.
>
> log_full
> log_full_01_11_2
22 matches
Mail list logo
