Re: [PERFORM] Bad query plan when the wrong data type is used

[Vitalii Tymchyshyn]

09.02.11 01:14, Dave Crooke написав(ла):
You will get the same behaviour from any database product where the 
query as written requires type coercion - the coercion has to go in 
the direction of the "wider" type. I have seen the exact same scenario 
with Oracle, and I view it as a problem with the way the query is 
written, not with the database server.


Whoever coded the application which is making this query presumably 
knows that the visa.id  field is an integer type in 
the schema they designed, so why are they passing a float? Convert the 
4.0 to 4 on the application side instead, it's one function call or cast.
Actually the problem may be in layers, and the  problem may even be not 
noticed until it's late enough. As far as I remember from this list 
there are problems with column being integer and parameter prepared as 
bigint or number. Same for number vs double vs float.

As for me it would be great for optimizer to consider the next:
1) val1::narrow = val2::wide as (val1::narrow = val2::narrow and 
val2::narrow = val2::wide)
2) val1::narrow < val2::wide as (val1::narrow < val2::narrow and 
val1::wide < val2::wide)
3) val1::narrow > val2::wide as (val1::narrow + 1 > val2::narrow and 
val1::wide > val2::wide)

Of course it should use additional check it this allows to use an index.
Surely, this is not an easy thing to implement, but as for me similar 
question are raised quite often in this list.


Best regards, Vitalii Tymchyshyn

Re: [PERFORM] Really really slow select count(*)

[felix]

On Tue, Feb 8, 2011 at 3:23 PM, Shaun Thomas  wrote:

>
> With 300k rows, count(*) isn't a good test, really. That's just on the edge
> of big-enough that it could be > 1-second to fetch from the disk controller,
>


1 second you say ?  excellent, sign me up

70 seconds is way out of bounds

I don't want a more efficient query to test with, I want the shitty query
that performs badly that isolates an obvious problem.

The default settings are not going to cut it for a database of your size,
> with the volume you say it's getting.
>

not to mention the map reduce jobs I'm hammering it with all night :)

but I did pause those until this is solved

But you need to put in those kernel parameters I suggested. And I know this
> sucks, but you also have to raise your shared_buffers and possibly your
> work_mem and then restart the DB. But this time, pg_ctl to invoke a fast
> stop, and then use the init script in /etc/init.d to restart it.


I'm getting another slicehost slice. hopefully I can clone the whole thing
over without doing a full install and go screw around with it there.

its a fairly complicated install, even with buildout doing most of the
configuration.


=felix

Re: [HACKERS] [PERFORM] Slow count(*) again...

[Gorshkov]

On 2011-02-03 22:48, Scott Marlowe wrote:

On Thu, Feb 3, 2011 at 8:40 PM, Greg Smith  wrote:

Scott Marlowe wrote:


Yes they're useful, but like a plastic bad covering a broken car window,
they're useful because they cover something that's inherently broken.



Awesome.  Now we have a car anology, with a funny typo no less.  "Plastic
bad", I love it.  This is real progress toward getting all the common list
argument idioms aired out.  All we need now is a homage to Mike Godwin and
we can close this down.


It's not so much a car analogy as a plastic bad analogy.




Don't be such an analogy Nazi.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance