----- Puvodní zpráva -----
Od: "Steve Atkins" <[EMAIL PROTECTED]>
Komu: <[EMAIL PROTECTED]>
Odesláno: 28. ledna 2004 20:32
Predmet: [GENERAL] Specifying many rows in a table
> I have a large table (potentially tens or hundreds of millions of rows) and
> I need to extract some number of these rows, defined by an integer primary
> key.
>
> So, the obvious answer is
>
> select * from table where id in (1,3,4);
Should not it be select * from table where id in ('1','3','4'); ?
ie add an single quote around the numbers ? Think I red some statement in
documentation, that without a single quote, index scan may not be always used ?
>
> But I may want to extract a large number of rows, many thousands
>
> select * from table where id in (1, 3, 5, ...., 100000, 100017, 23000);
>
> This falls over when it exceeds the maximum expression depth of 10,000.
> And I have a sneaky feeling that increasing max_expr_depth isn't the
> right fix.
>
> Performance is pretty important, so does anyone have a good suggestion
> for how to phrase this query so that it'll at worst only be a single
> seq-scan through the mondo table, and make a reasonable choice as to
> whether to use an index-scan or seq-scan, depending on the number
> of rows I'm pulling out?
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
