## Re: Optimization idea

From: Robert Haas Cédric Villemain Vlad Arkhipov , pgsql-performance(at)postgresql(dot)org Re: Optimization idea 2010-04-28 00:46:36 q2w603c8f071004271746s43f8669cz45bec5914b1fa9e0@mail.gmail.com (view raw, whole thread or download thread mbox) 2010-04-22 09:25:46 from Vlad Arkhipov  2010-04-22 12:37:32 from Greg Smith   2010-04-23 02:37:57 from Vlad Arkhipov    2010-04-23 11:05:53 from Robert Haas     2010-04-23 13:09:34 from Cédric Villemain      2010-04-23 13:36:40 from Robert Haas       2010-04-23 19:22:08 from Cédric Villemain        2010-04-23 22:06:26 from Robert Haas         2010-04-23 22:53:34 from Tom Lane          2010-04-23 23:09:39 from Robert Haas      2010-04-23 13:41:01 from "Kevin Grittner"     2010-04-26 02:52:23 from Vlad Arkhipov      2010-04-26 09:33:29 from Cédric Villemain       2010-04-28 00:46:36 from Robert Haas        2010-04-28 07:49:05 from Cédric Villemain         2010-04-28 09:38:20 from Vlad Arkhipov         2010-04-29 03:17:25 from Robert Haas          2010-04-29 08:21:13 from Cédric Villemain        2010-05-01 10:52:35 from Cédric Villemain         2010-05-01 12:00:32 from Cédric Villemain   2010-04-23 04:13:49 from Vlad Arkhipov pgsql-performance
```On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
<cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
> In the first query, the planner doesn't use the information of the 2,3,4.
> It just does a : I'll bet I'll have 2 rows in t1 (I think it should
> say 3, but it doesn't)
> So it divide the estimated number of rows in the t2 table by 5
> (different values) and multiply by 2 (rows) : 40040.

I think it's doing something more complicated.  See scalararraysel().

> In the second query the planner use a different behavior : it did
> expand the value of t1.t to t2.t for each join relation and find a
> costless plan. (than the one using seqscan on t2)

I think the problem here is one we've discussed before: if the query
planner knows that something is true of x (like, say, x =
ANY('{2,3,4}')) and it also knows that x = y, it doesn't infer that
the same thing holds of y (i.e. y = ANY('{2,3,4}') unless the thing
that is known to be true of x is that x is equal to some constant.
Tom doesn't think it would be worth the additional CPU time that it
would take to make these sorts of deductions.  I'm not sure I believe
that, but I haven't tried to write the code, either.

...Robert

```

### pgsql-performance by date

 Next: From: Robert Haas Date: 2010-04-28 00:55:11 Subject: Re: autovacuum strategy / parameters Previous: From: Greg Spiegelberg Date: 2010-04-27 03:59:50 Subject: Re: tmpfs and postgres memory