Re: [HACKERS] Use unique index for longer pathkeys.

2014-08-07 Thread Kyotaro HORIGUCHI
Hello, Although, yes, you're right, irrespective of the common something, and even if the dropped index was i_t1_pkey_2, which is on t1(a, b), the result tuples are sorted as expected only by the pathkey (t.a = t1.a, t1.b). It is because both t and t1 are still unique so the joined

Re: [HACKERS] Use unique index for longer pathkeys.

2014-08-04 Thread Kyotaro HORIGUCHI
Hello, Now drop the i_t1_pkey_1 and check the query plan again. drop index i_t1_pkey_1; explain (costs off, analyze off) select * from t,t1 where t.a=t1.a order by t1.a,t1.b,t1.c,t1.d; QUERY PLAN Sort

Re: [HACKERS] Use unique index for longer pathkeys.

2014-08-04 Thread Amit Kapila
On Mon, Aug 4, 2014 at 1:22 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, I think irrespective of that we can trim t1.c t1.d as we have primary key (unique and non column) for t1.a, t1.b. Basically even if we don't use the primary key index, we can still trim

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-29 Thread Amit Kapila
On Mon, Jul 28, 2014 at 3:17 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Now drop the i_t1_pkey_1 and check the query plan again. drop index i_t1_pkey_1; explain (costs off, analyze off) select * from t,t1 where t.a=t1.a order by t1.a,t1.b,t1.c,t1.d;

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-28 Thread Kyotaro HORIGUCHI
Hello, I think there is one more disadvantage in the way current patch is done which is that you need to collect index path keys for all relations irrespective of whether they will be of any use to eliminate useless pathkeys from query_pathkeys. One trivial case that comes to mind is

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-26 Thread Amit Kapila
On Fri, Jul 25, 2014 at 12:48 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: I think there is one more disadvantage in the way current patch is done which is that you need to collect index path keys for all relations irrespective of whether they will be of any use to eliminate

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-26 Thread Amit Kapila
On Sat, Jul 26, 2014 at 11:53 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Fri, Jul 25, 2014 at 12:48 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: I think there is one more disadvantage in the way current patch is done which is that you need to collect index path keys

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-25 Thread Kyotaro HORIGUCHI
Hello. I've been cooled off and convinced that this patch has become quite useless by itself. It improves almost only UNIONs with ORDER BY on tables that have unioform primary keys, and needs my another patch to work. I'll try to reintegrate this patch into my 'another patch' as mentioned below

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-24 Thread Robert Haas
On Tue, Jul 22, 2014 at 5:19 AM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: # By the way, this style of calling a person is quite common # among Japanese since the first-name basis implies very close # relationship or it frequently conveys offensive shade. But I'm # not sure what

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-24 Thread Amit Kapila
On Tue, Jul 22, 2014 at 2:49 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Sorry , previous version has bugs. It stamps over the stack and crashesX( The attached is the bug fixed version, with no substantial changes. On Tue, Jul 15, 2014 at 2:17 PM, Kyotaro HORIGUCHI

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-22 Thread Kyotaro HORIGUCHI
Sorry , previous version has bugs. It stamps over the stack and crashesX( The attached is the bug fixed version, with no substantial changes. On Tue, Jul 15, 2014 at 2:17 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hi, the attached is the revised version. Thanks

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-19 Thread Amit Kapila
On Tue, Jul 15, 2014 at 2:17 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hi, the attached is the revised version. Thanks Horiguchi-San for the updated patch. Today while looking into updated patch, I was wondering why can't we eliminate useless keys in query_pathkeys when we

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-15 Thread Kyotaro HORIGUCHI
Hi, the attached is the revised version. - defined allnotnull instead of full_ordered in RelOptInfo and changed to use it. - compare_pathkeys_ignoring_strategy() is now integrated into compare_pathkeys(). - index pathkeys caching in RelOptInfo added. (build_index_pathkeys() does)

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-14 Thread Kyotaro HORIGUCHI
Thank you for reviewing, the revised patch will come later. At Mon, 14 Jul 2014 11:01:52 +0530, Amit Kapila amit.kapil...@gmail.com wrote in caa4ek1+6b6wjwf51ozmrl+mkfh8xup9j-pehqvor8se7swy...@mail.gmail.com On Fri, Jun 13, 2014 at 1:11 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-14 Thread Amit Kapila
On Mon, Jul 14, 2014 at 4:02 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: At Mon, 14 Jul 2014 11:01:52 +0530, Amit Kapila amit.kapil...@gmail.com wrote in caa4ek1+6b6wjwf51ozmrl+mkfh8xup9j-pehqvor8se7swy...@mail.gmail.com On Fri, Jun 13, 2014 at 1:11 PM, Kyotaro HORIGUCHI

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-13 Thread Amit Kapila
On Fri, Jun 13, 2014 at 1:11 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, This is the continuation from the last CF. This patch intends to make PG to use index for longer pathkeys than index columns when, - The index is a unique index. - All index columns are NOT

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-10 Thread Kyotaro HORIGUCHI
Thank you for taking a look on this patch. I took a quick look at this patch, more or less because nobody else did. Duing last CF, I proposed to match long pathkeys against index columns during creating index paths. This worked fine but also it is difficult to make sure that all

Re: [HACKERS] Use unique index for longer pathkeys.

2014-07-07 Thread Abhijit Menon-Sen
Hi. I took a quick look at this patch, more or less because nobody else did. Duing last CF, I proposed to match long pathkeys against index columns during creating index paths. This worked fine but also it is difficult to make sure that all side-effects are eliminated. Finally Tom Lane

[HACKERS] Use unique index for longer pathkeys.

2014-06-13 Thread Kyotaro HORIGUCHI
Hello, This is the continuation from the last CF. This patch intends to make PG to use index for longer pathkeys than index columns when, - The index is a unique index. - All index columns are NOT NULL. - The index column list is a subset of query_pathkeys. The use cases for this patch are,