Re: [HACKERS] Merge Join with an Index Optimization

On Sun, Sep 11, 2016 at 9:20 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Michael Malis <michaelmal...@gmail.com> writes: > >> As I understand it, a merge join will currently read all tuples from > both > >> subqueries (besides early termination). I believe

[HACKERS] Merge Join with an Index Optimization

Hi, As I understand it, a merge join will currently read all tuples from both subqueries (besides early termination). I believe it should be possible to take advantages of the indexes on one or both of the tables being read from to skip a large number of tuples that would currently be read. As an

Re: [HACKERS] Merge Join with an Index Optimization

I discovered that the kind of join I proposed is called the leapfrog triejoin: https://arxiv.org/pdf/1210.0481v5.pdf

[HACKERS] Highly Variable Planning Times

Hi, I've been encountering highly variable planning time on PG 9.5.4. Running `EXPLAIN SELECT * FROM events_1171738` will take anywhere from 200ms to 4s. This likely has to do with the table having 1300 partial indexes on it (for reasons elaborated on in

Re: [HACKERS] Highly Variable Planning Times

> TBH, I see no convincing explanation in that article of why 1300 partial > indexes are a good idea. I don't like it much either. I've been trying to eliminate most of the need for the partial indexes, but this is the current state of things. > *At best*, you're doing substantial work in the >

Re: [HACKERS] Highly Variable Planning Times

> The profile seems to confirm that this is largely due to cache misses. Can you elaborate? Cache misses of what? Why is the planning time so variable? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Highly Variable Planning Times

Thanks for the help Andres. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Poor cost estimate with interaction between table correlation and partial indexes

Hi. I'm looking to get started contributing code to Postgres. A small issue I'm aware of that I think would make a good first contribution is a poor cost estimate made due to an interaction between table correlation and partial indexes. Currently the planner assumes that when an index is

[HACKERS] Re: Poor cost estimate with interaction between table correlation and partial indexes

Do you think this is a reasonable approach? Should I start working on a patch based on the solution I described or is there some other approach I should look into? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Re: Poor cost estimate with interaction between table correlation and partial indexes

(Sorry David. I initially replied only to you) Ok. I've attached a patch of a proof-of-concept. I have a few questions about tests. What is typical workflow to add tests for changes to the planner? Also I ran make check and it appears one of the existing tests is failing. What is a typical way

Re: [HACKERS] Re: Poor cost estimate with interaction between table correlation and partial indexes

Hmm... It seems the command I used for obtaining a patch I got from here https://wiki.postgresql.org/wiki/Working_with_Git truncated part of the patch. I've attached the file generated from git diff --patience master improve-partial-index-correlation-calculation --no-color >