headers
Rajesh Kumar Mallah | 1 Aug 2003 05:40

Re: Views With Unions

Stephan Szabo wrote:

>On Thu, 31 Jul 2003, Christopher Browne wrote:
>
>  
>
>>  select * from log_table where request_time between 'june 11 2003' and
>>                                                   'june 12 2003';
>>
>>returns a plan:
>>Subquery Scan log_table  (cost=0.00..10950.26 rows=177126 width=314)
>>  ->  Append  (cost=0.00..10950.26 rows=177126 width=314)
>>        ->  Subquery Scan *SELECT* 1  (cost=0.00..3089.07 rows=50307 width=71)
>>              ->  Seq Scan on log_table_1  (cost=0.00..3089.07 rows=50307 width=71)
>>        ->  Subquery Scan *SELECT* 2  (cost=0.00..602.92 rows=9892 width=314)
>>              ->  Seq Scan on log_table_2  (cost=0.00..602.92 rows=9892 width=314)
>>        ->  Subquery Scan *SELECT* 3  (cost=0.00..2390.09 rows=39209 width=314)
>>              ->  Seq Scan on log_table_3  (cost=0.00..2390.09 rows=39209 width=314)
>>    
>>
>
>What version are you using?  In 7.3 and up it should be willing to
>consider moving the clause down, unless there's something like a type
>mismatch (because in that case it may not be equivalent without a bunch
>more work on the clause).
>
Dear Chris,

 I had the same problem(type mismatch)  and it was  solved finally. 
check the list
(Continue reading)

Permalink | Reply | 0 comments |
headers
Andrew Sullivan | 1 Aug 2003 14:16

Re: Odd explain estimate

On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote:
>  
> Well, if I don't do this it wants to seqscan a table that occupies 350k
> pages, instead of pulling a couple thousand rows. I started running it
> with the seqscan and it's already taken way longer than it does if I
> disable seqscan.

That was indeed the question. 

If it uses a seqscan when it ought not to do, then there's something
wrong with the statistics, or you haven't vacuum analysed correctly,
or your table needs vacuum full (is it really 350k pages, or is that
mostly dead space?), &c. -- all the usual bad-seqscan candidates.

enable_seqscan=off is probably not a good strategy for any moderately
complicated query.  If the planner were perfect, of course, you'd
never need it at all.

A

--

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew <at> libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
(Continue reading)

Permalink | Reply | 1 comment |
headers
Christopher Browne | 1 Aug 2003 14:20

Re: Views With Unions


Stephan Szabo said:
>
> On Thu, 31 Jul 2003, Christopher Browne wrote:
>
>>   select * from log_table where request_time between 'june 11 2003'
>> and
>>                                                    'june 12 2003';
>>
>> returns a plan:
>> Subquery Scan log_table  (cost=0.00..10950.26 rows=177126 width=314)
>>   ->  Append  (cost=0.00..10950.26 rows=177126 width=314)
>>         ->  Subquery Scan *SELECT* 1  (cost=0.00..3089.07 rows=50307
>> width=71)
>>               ->  Seq Scan on log_table_1  (cost=0.00..3089.07
>> rows=50307 width=71)
>>         ->  Subquery Scan *SELECT* 2  (cost=0.00..602.92 rows=9892
>> width=314)
>>               ->  Seq Scan on log_table_2  (cost=0.00..602.92
>> rows=9892 width=314)
>>         ->  Subquery Scan *SELECT* 3  (cost=0.00..2390.09 rows=39209
>> width=314)
>>               ->  Seq Scan on log_table_3  (cost=0.00..2390.09
>> rows=39209 width=314)
>
> What version are you using?  In 7.3 and up it should be willing to
> consider moving the clause down, unless there's something like a type
> mismatch (because in that case it may not be equivalent without a bunch
> more work on the clause).
(Continue reading)

Permalink | Reply | 3 comments |
headers
Stephan Szabo | 1 Aug 2003 17:10

Re: Views With Unions

On Fri, 1 Aug 2003, Christopher Browne wrote:

> Stephan Szabo said:
> >
> >
> > What version are you using?  In 7.3 and up it should be willing to
> > consider moving the clause down, unless there's something like a type
> > mismatch (because in that case it may not be equivalent without a bunch
> > more work on the clause).
>
> That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
>
> Which provides four findings:
>
> 1.  On 7.2.4, adding additional type info just doesn't help, fitting with
> the notion that, consistent with your comment, improvement wouldn't happen
> earlier than 7.3.
>
> There's no help on 7.2 :-(, and the system I'm initially most interested
> in using this on is still on 7.2.

If you really wanted you could try going back and finding the diffs
associated with this in the CVS history or committers archives and see if
you can make equivalent changes to 7.2, but that's possibly going to be
difficult.

> 2.  When I retried on 7.4, it _did_ find search paths based on Index Scan,
> when I added in additional type information.  So the optimization I was
> wishing for _is_ there :-).  In the longer term, that's very good news.
>
(Continue reading)

Permalink | Reply | 2 comments |
headers
Christopher Browne | 1 Aug 2003 17:48

Re: Views With Unions

Stephan Szabo <sszabo <at> megazone.bigpanda.com> writes:
> On Fri, 1 Aug 2003, Christopher Browne wrote:
>> Stephan Szabo said:
>> > What version are you using?  In 7.3 and up it should be willing to
>> > consider moving the clause down, unless there's something like a type
>> > mismatch (because in that case it may not be equivalent without a bunch
>> > more work on the clause).
>>
>> That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
>>
>> Which provides four findings:
>>
>> 1.  On 7.2.4, adding additional type info just doesn't help, fitting with
>> the notion that, consistent with your comment, improvement wouldn't happen
>> earlier than 7.3.
>>
>> There's no help on 7.2 :-(, and the system I'm initially most interested
>> in using this on is still on 7.2.
>
> If you really wanted you could try going back and finding the diffs
> associated with this in the CVS history or committers archives and see if
> you can make equivalent changes to 7.2, but that's possibly going to be
> difficult.

Somehow I don't think that'll fly; I have taken a brief look at some
of the optimizer code, and I very much don't want to leap into that at
the moment.  (I don't imagine I'd be able to muster much enthusiasm
for the idea from others that are involved, either.  More likely, I'm
understating the probable opposition to the idea...  :-))
(Continue reading)

Permalink | Reply | 1 comment |
headers
Francisco Reyes | 1 Aug 2003 18:08

How number of columns affects performance

If a table which will be heavily used has numerous fields, yet only a
handfull of them will be used heavily, would it make sense performance wise to split it?

Example
Table 1
Field 1
....
Field 100

Table 2
References Field 1 of table1
.....

Table n
References Field 1 of table 1

So table 1 basically will be referenced by many tables and most of the
time only a handfull of fields  of table 1 are needed. Don't have exact
numbers, but let's say that more than 60% of queries to table 1 queries
only use 20 fields or less.

If I split Table 1 then the second table will basically be a 1 to 1 to
Table 1.

I have this simmilar scenario for two tables. One is close to 1 Million
records and the other is about 300,000 records.

Programming wise it is much easier to only have one table, but I am just
concerned about performance.
(Continue reading)

Permalink | Reply | 8 comments |
headers
Ron Johnson | 1 Aug 2003 18:34
Picon
Gravatar

Re: How number of columns affects performance

On Fri, 2003-08-01 at 11:08, Francisco Reyes wrote:
> If a table which will be heavily used has numerous fields, yet only a
> handfull of them will be used heavily, would it make sense performance wise to split it?
> 
> Example
> Table 1
> Field 1
> ....
> Field 100
> 
> Table 2
> References Field 1 of table1
> .....
> 
> Table n
> References Field 1 of table 1
> 
> So table 1 basically will be referenced by many tables and most of the
> time only a handfull of fields  of table 1 are needed. Don't have exact
> numbers, but let's say that more than 60% of queries to table 1 queries
> only use 20 fields or less.
> 
> If I split Table 1 then the second table will basically be a 1 to 1 to
> Table 1.

Do all 100 fields *really* all refer to the same *one* entity,
with no repeating values, etc?
If not, then good design says to split the table.

Also, if it's a high-activity table, but you only rarely need fields
(Continue reading)

Permalink | Reply | 7 comments |
headers
Tom Lane | 1 Aug 2003 18:51
Picon

Re: Views With Unions

Christopher Browne <cbbrowne <at> libertyrms.info> writes:
> The DOMAIN case I mentioned the other day had something odd going on
> that LOST the type information associated with the domain.  Albeit
> that was on 7.3, whereas the changes in DOMAIN functionality that make
> them meaningfully useful come in 7.4...

Domains were a work-in-progress in 7.3, and to some extent still are.
Please try to test out 7.4beta and let us know about deficiencies you
find.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 0 comments |
headers
Francisco J Reyes | 1 Aug 2003 19:14

Re: How number of columns affects performance

On Fri, 1 Aug 2003, Ron Johnson wrote:

> Do all 100 fields *really* all refer to the same *one* entity,
> with no repeating values, etc?

Yes all fields belong to the same entity. I used 100 as an example it may
be something like 60 to 80 fields (there are two tables in question). I
don't formally do 3rd normal form, but for the most part I do most of
the general concepts of normalization.

> If not, then good design says to split the table.

The original data was in Foxpro tables and I have made better normalized
tables in PostgreSQL.

> Also, if it's a high-activity table, but you only rarely need fields
> 60-90, then splitting them out to their own table might be useful
> (especially if some of those fields are large *CHAR or TEXT).

Yes some of the fields are varchars. 5 fields are varchar(22) and 3 longer
(35, 58, 70). The total row length is a little over 400 characters in
Foxpro. In postgreSQL may be less than 300 (ie Foxpro uses ASCII
representation for numbers so to store "1234567" it uses 7 bytes, whereas
in PostgreSQL I can just make it an int and use 4 bytes)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
(Continue reading)

Permalink | Reply | 6 comments |
headers
Ron Johnson | 1 Aug 2003 19:32
Picon
Gravatar

Re: How number of columns affects performance

On Fri, 2003-08-01 at 12:14, Francisco J Reyes wrote:
> On Fri, 1 Aug 2003, Ron Johnson wrote:
> 
> > Do all 100 fields *really* all refer to the same *one* entity,
> > with no repeating values, etc?
> 
> Yes all fields belong to the same entity. I used 100 as an example it may
> be something like 60 to 80 fields (there are two tables in question). I
> don't formally do 3rd normal form, but for the most part I do most of
> the general concepts of normalization.

Woo hoo!!

> Yes some of the fields are varchars. 5 fields are varchar(22) and 3 longer
> (35, 58, 70). The total row length is a little over 400 characters in
> Foxpro. In postgreSQL may be less than 300 (ie Foxpro uses ASCII
> representation for numbers so to store "1234567" it uses 7 bytes, whereas
> in PostgreSQL I can just make it an int and use 4 bytes)

But I'd only split if these big field are rarely used.  Note that
VARCHAR(xx) removes trailing spaces, so that also is a factor.

--

-- 
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson <at> cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
(Continue reading)

Permalink | Reply | 0 comments |

Gmane