Re: Specifying many rows in a table

----- Puvodní zpráva -----
Od: "Steve Atkins" <steve@blighty.com>
Komu: <pgsql-general@postgresql.org>
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,
indexscan 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
>
>